public static DataTable GetUserUsingUserNamePasswordAsDataTable(string userName, string password)
{
var fields = new ResultsetFields(4);
fields.DefineField(UserFields.Id, 0);
fields.DefineField(UserFields.FullName, 1);
fields.DefineField(UserFields.IsAdmin, 2);
fields.DefineField(UserFields.IsInstructor, 3);
var filter = new PredicateExpression
{
(UserFields.Username == userName | UserFields.Email == userName),
UserFields.Password == password
};
var relations = new RelationCollection()
{
};
var toReturn = new DataTable();
var dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, toReturn, 0, null, filter, relations, true, null, null, 0, 0);
return(toReturn);
}
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
RelationCollection locationToMessage = new RelationCollection
{
new EntityRelation(LocationFields.LocationId, DeviceFields.LocationId, RelationType.OneToOne),
new EntityRelation(DeviceFields.DeviceId, DeviceMessageFields.DeviceId, RelationType.OneToOne),
new EntityRelation(DeviceMessageFields.MessageId, MessageFields.MessageId, RelationType.OneToOne)
};
ResultsetFields fields = new ResultsetFields(7);
fields.DefineField(MessageFields.MessageId, 0, "MessageId");
fields.DefineField(MessageFields.Title, 1, "Title");
fields.DefineField(MessageFields.Body, 2, "Body");
fields.DefineField(MessageFields.CreateTime, 3, "CreateTime");
fields.DefineField(MessageFields.StartTime, 4, "StartTime");
fields.DefineField(MessageFields.EndTime, 5, "EndTime");
fields.DefineField(LocationFields.Name, 6, "LocationName");
DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
if (Roles.IsUserInRole("Service Administrator"))
{
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null, locationToMessage, true, null, null, 0, 0);
}
else if (Roles.IsUserInRole("Organization Administrator"))
{
dao.GetMultiAsDataTable(fields, dynamicList, 0, null,
new PredicateExpression(LocationFields.OrganizationId == Membership.GetUser().GetUserId().OrganizationId),
locationToMessage, true, null, null, 0, 0);
}
//else
// messages.GetMulti(EcMessageBankFields.CustomerLocationId == user.LocationId);
if (dynamicList.Rows.Count > 0)
{
bool alternate = false;
foreach (DataRow message in dynamicList.Rows)
{
TableRow messageHeader = new TableRow()
{
CssClass = alternate ? "alternate" : ""
};
messageHeader.Controls.Add(new TableCell()
{
Text = message["CreateTime"].ToString()
});
messageHeader.Controls.Add(new TableCell()
{
Text = message["LocationName"].ToString()
});
MainTable.Controls.Add(messageHeader);
TableRow messageBody = new TableRow()
{
CssClass = alternate ? "alternate" : ""
};
messageBody.Controls.Add(new TableCell()
{
ColumnSpan = 2,
Text =
"<strong style='float:left; display:inline-block; padding-right:5px;'>" +
message["Title"] + "</strong>" + message["Body"]
});
MainTable.Controls.Add(messageBody);
alternate = !alternate;
}
}
else
{
TableRow empty_list = new TableRow();
empty_list.Controls.Add(new TableCell()
{
Text = "No messages to display."
});
MainTable.Controls.Add(empty_list);
}
}
/// <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;
}