/// <summary>
/// Gets various order statistics from the orders table
/// </summary>
/// <param name="averageOrderPrice">The average order price.</param>
/// <param name="highestOrderPrice">The higest order price.</param>
/// <param name="numberOfOrders">The number of orders.</param>
/// <param name="orderIdWithHighestPrice">The order id with highest price.</param>
/// <remarks>No error checking is applied to this routine, it's for illustrating the framework functionality.</remarks>
public void GetOrderStatistics(out decimal averageOrderPrice, out decimal highestOrderPrice, out int numberOfOrders, out int orderIdWithHighestPrice)
{
Console.WriteLine("GetOrderStatistics called");
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
// get all order prices using a dynamic list.
ResultsetFields orderPricesFields = new ResultsetFields(2);
orderPricesFields.DefineField(OrderDetailsFields.OrderId, 0, "OrderId");
orderPricesFields.DefineField(OrderDetailsFields.ProductId, 1, "OrderPrice", "", AggregateFunction.Sum);
orderPricesFields[1].ExpressionToApply = (OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice);
IGroupByCollection groupBy = new GroupByCollection();
groupBy.Add(orderPricesFields[0]);
// fetch in a datatable, orderid + the order price.
DataTable orderPrices = new DataTable();
adapter.FetchTypedList(orderPricesFields, orderPrices, null, 0, null, true, groupBy);
// calculate average order price and which customer has the most expensive order
averageOrderPrice = 0.0M;
highestOrderPrice = 0.0M;
orderIdWithHighestPrice = 0;
for (int i = 0; i < orderPrices.Rows.Count; i++)
{
decimal currentOrderPrice = (decimal)orderPrices.Rows[i]["OrderPrice"];
if (currentOrderPrice > highestOrderPrice)
{
highestOrderPrice = currentOrderPrice;
orderIdWithHighestPrice = (int)orderPrices.Rows[i]["OrderId"];
}
averageOrderPrice += currentOrderPrice;
}
averageOrderPrice = averageOrderPrice / orderPrices.Rows.Count;
numberOfOrders = orderPrices.Rows.Count;
}
}
/// <summary>
/// Gets the customer with the most orders and also the customers' number of orders.
/// </summary>
/// <param name="numberOfOrders">The number of orders.</param>
/// <returns>the CustomerEntity with the most orders and also the number of orders of this customer</returns>
/// <remarks>No error checking is applied to this routine, it's for illustrating the framework functionality.</remarks>
public CustomerEntity GetCustomerWithMostOrdersAndNumberOfOrders(out int numberOfOrders)
{
Console.WriteLine("GetCustomerWithMostOrdersAndNumberOfOrders called");
CustomerEntity toReturn = null;
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
// create a list to obtain the customerid + # of orders. We'll just fetch the 1st row, ordering on the # of orders.
ResultsetFields orderCustomerFields = new ResultsetFields(2);
orderCustomerFields.DefineField(OrderFields.CustomerId, 0, "CustomerId");
orderCustomerFields.DefineField(OrderFields.OrderId, 1, "NumberOfOrders", "", AggregateFunction.Count);
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(orderCustomerFields[0]);
SortExpression sorter = new SortExpression();
sorter.Add(new SortClause(orderCustomerFields[1], null, SortOperator.Descending));
// now fetch the list, specify to fetch just 1 row, ordered descending on amount
DataTable orderCustomer = new DataTable();
adapter.FetchTypedList(orderCustomerFields, orderCustomer, null, 1, sorter, true, groupBy);
numberOfOrders = (int)orderCustomer.Rows[0]["NumberOfOrders"];
// we'll assume the data was there, so there is a row.
toReturn = new CustomerEntity(orderCustomer.Rows[0]["CustomerId"].ToString());
adapter.FetchEntity(toReturn);
}
return(toReturn);
}
public virtual void GroupBy(GroupByCollection groupBy)
{
bool first = true;
foreach (GroupByField groupByField in groupBy)
{
if (first)
{
Append(" GROUP BY ");
first = false;
}
else
{
Append(", ");
}
Field(groupByField.Field);
}
}
public OrderedPair <ResultsetFields, GroupByCollection> CreateMedicalVendorInvoiceStatisticFieldsAndGroupByClause()
{
var resultsetFields = new ResultsetFields(5);
resultsetFields.DefineField(PhysicianInvoiceItemFields.PhysicianInvoiceId, 0, "MedicalVendorInvoiceId");
resultsetFields.DefineField(PhysicianInvoiceItemFields.PhysicianInvoiceId, 1, "NumberOfEvaluations", AggregateFunction.Count);
resultsetFields.DefineField(PhysicianInvoiceItemFields.AmountEarned, 2, "InvoiceAmount", AggregateFunction.Sum);
//Need to map from Physican Evaluation
//resultsetFields.DefineField(PhysicianInvoiceItemFields.EvaluationStartTime, 3, "EvaluationStartTime");
//resultsetFields.DefineField(PhysicianInvoiceItemFields.EvaluationEndTime, 4, "EvaluationEndTime");
var groupByCollection = new GroupByCollection(resultsetFields[0])
{
resultsetFields[3], resultsetFields[4]
};
return(new OrderedPair <ResultsetFields, GroupByCollection>(resultsetFields, groupByCollection));
}
/// <summary>Gets the amount of rows in the database for this typed list.</summary>
/// <param name="allowDuplicates">Flag to allow duplicate rows (true) or not (false)</param>
/// <param name="filter">The filter to apply for the count retrieval</param>
/// <param name="groupByClause">group by clause to embed in the query</param>
/// <returns>the number of rows in the set defined by the passed in query elements</returns>
public virtual int GetDbCount(bool allowDuplicates, IPredicateExpression filter, GroupByCollection groupByClause)
{
IEntityFields fieldsInResultset = EntityFieldsFactory.CreateTypedViewEntityFieldsObject(TypedViewType.ProductSalesFor1997TypedView);
return(DAOFactory.CreateTypedListDAO().GetDbCount(fieldsInResultset, null, filter, null, groupByClause, allowDuplicates));
}
/// <summary>
/// Calculates the statistics for the Northwind database and shows them in the form, illustrating the
/// direct database power LLBLGen Pro offers you through scalar functions, aggregates, group by and expressions.
/// </summary>
/// <remarks>No error checking is applied to this routine, it's for illustrating the framework functionality.</remarks>
private void CalculateStatistics()
{
// get amount customers
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
int amountCustomers = (int)adapter.GetScalar(CustomerFields.CustomerId, AggregateFunction.CountRow);
_amountCustomersTextBox.Text = amountCustomers.ToString();
// get all order prices
ResultsetFields orderPricesFields = new ResultsetFields(2);
orderPricesFields.DefineField(OrderDetailFields.OrderId, 0, "OrderId");
orderPricesFields.DefineField(OrderDetailFields.ProductId, 1, "OrderPrice", "", AggregateFunction.Sum);
orderPricesFields[1].ExpressionToApply = (OrderDetailFields.Quantity * OrderDetailFields.UnitPrice);
IGroupByCollection groupBy = new GroupByCollection();
groupBy.Add(orderPricesFields[0]);
// fetch in a datatable, orderid + the order price.
DataTable orderPrices = new DataTable();
adapter.FetchTypedList(orderPricesFields, orderPrices, null, 0, null, true, groupBy);
// calculate average order price and which customer has the most expensive order
decimal averageOrderPrice = 0.0M;
decimal highestOrderPrice = 0.0M;
int orderIdWithHighestPrice = 0;
for (int i = 0; i < orderPrices.Rows.Count; i++)
{
decimal currentOrderPrice = (decimal)orderPrices.Rows[i]["OrderPrice"];
if (currentOrderPrice > highestOrderPrice)
{
highestOrderPrice = currentOrderPrice;
orderIdWithHighestPrice = (int)orderPrices.Rows[i]["OrderId"];
}
averageOrderPrice += currentOrderPrice;
}
averageOrderPrice = averageOrderPrice / orderPrices.Rows.Count;
_highestOrderPriceTextBox.Text = highestOrderPrice.ToString("C");
_averageOrderPriceTextBox.Text = averageOrderPrice.ToString("C");
// get order with highest price. Pull customer also in 1 go
IPrefetchPath2 prefetchPathCustomer = new PrefetchPath2((int)EntityType.OrderEntity);
prefetchPathCustomer.Add(OrderEntity.PrefetchPathCustomer);
OrderEntity highestOrder = new OrderEntity(orderIdWithHighestPrice);
adapter.FetchEntity(highestOrder, prefetchPathCustomer);
_customerWithHighestOrder = highestOrder.Customer; // already loaded through prefetch path.
_highestOrderCustomerTextBox.Text = _customerWithHighestOrder.CompanyName;
// get customer with most orders.
ResultsetFields orderCustomerFields = new ResultsetFields(2);
orderCustomerFields.DefineField(OrderFields.CustomerId, 0, "CustomerId");
orderCustomerFields.DefineField(OrderFields.OrderId, 1, "AmountOrders", "", AggregateFunction.Count);
groupBy = new GroupByCollection();
groupBy.Add(orderCustomerFields[0]);
SortExpression sorter = new SortExpression();
sorter.Add(new SortClause(orderCustomerFields[1], null, SortOperator.Descending));
// now fetch the list, just 1 row, ordered descending on amount
DataTable orderCustomer = new DataTable();
adapter.FetchTypedList(orderCustomerFields, orderCustomer, null, 1, sorter, true, groupBy);
_mostOrdersPerCustomerTextBox.Text = orderCustomer.Rows[0]["AmountOrders"].ToString();
// we'll assume the data was there, so there is a row.
_customerWithMostOrders = new CustomerEntity(orderCustomer.Rows[0]["CustomerId"].ToString());
adapter.FetchEntity(_customerWithMostOrders);
_mostOrdersCustomerTextBox.Text = _customerWithMostOrders.CompanyName;
}
}
public void OperatorGroupBy()
{
GroupByCollection gb = "ContactNaam";
}