/// <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
CustomerCollection customers = new CustomerCollection();
int amountCustomers = customers.GetDbCount();
_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();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(orderPricesFields, orderPrices, 0, null, null, null, true, groupBy, null, 0, 0);
// 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
IPrefetchPath prefetchPathCustomer = new PrefetchPath((int)EntityType.OrderEntity);
prefetchPathCustomer.Add(OrderEntity.PrefetchPathCustomer);
OrderEntity highestOrder = new OrderEntity();
highestOrder.FetchUsingPK(orderIdWithHighestPrice, 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], SortOperator.Descending));
// now fetch the list, just 1 row, ordered descending on amount
DataTable orderCustomer = new DataTable();
dao.GetMultiAsDataTable(orderCustomerFields, orderCustomer, 1, sorter, null, null, true, groupBy, null, 0, 0);
_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());
_mostOrdersCustomerTextBox.Text = _customerWithMostOrders.CompanyName;
}
