/// <summary>
/// Returns a Select with aggregate functions
/// </summary>
/// <param name="aggegateSelectFields">
/// Aggregate fields definitions
/// </param>
/// <param name="table">
/// Table to query
/// </param>
/// <param name="filters">
/// Filters to apply on Select
/// </param>
/// <param name="columnsToGroup">
/// Columns for group by
/// </param>
/// <returns>
/// Select Sentence with aggregate functions
/// </returns>
public virtual Command Select(SelectAggregate select)
{
//Validating arguments
if (select == null)
{
throw new ArgumentNullException("select");
}
//Building the select sentence
Command command = "SELECT ";
//Crossing the fields definitions
foreach (var column in select.AggregateColumns)
{
command.Script += SelectAggregateColumn(column) + ", ";
}
//Removing the last commas
command.Script = command.Script.TrimEnd(',', ' ') + " ";
command.Append(FromClause(select));
command.Append(WhereClause(select.Where, LogicalOperator.And));
command.Append(GroupByClause(select.GroupBy));
command.Append(OrderByClause(select.OrderBy));
command.Append(LimitClause(select.Limit));
command.Script += ScriptSeparator;
//Returning the result
return(command);
}
/// <summary>
/// Constructs the grid and populates it with data
/// </summary>
public void DataBind()
{
Content = Platform.Current.Create <IGrid>();
var db = DataBase.CreateDataBase();
//if DataSource has no members defined, we will use the default members
if (DataSource.Members.Count == 0)
{
var defaultMembers = DataSource.DataType.DataMembers.Where(dm => dm.SelectByDefault);
//if there are no default members, we use all f*****g members
if (defaultMembers.Count() == 0)
{
defaultMembers = DataSource.DataType.DataMembers;
}
foreach (var defaultDataMember in defaultMembers)
{
DataSource.Members.Add(new SelectMember(defaultDataMember));
}
}
//find out the total number of records
SelectAggregate count = new SelectAggregate();
count.DataType = DataSource.DataType;
count.Joins.AddRange(DataSource.Joins);
count.Where.AddRange(DataSource.Where);
count.AggregateMembers.Add(new SelectAggregateMember(DataSource.DataType.PrimaryKey.First(), SelectAggregateFunction.Count));
long totalrecordsCount = (long)db.SelectScalar(count);
int pageSize = DataSource.Limit == null ? 0 : DataSource.Limit.Count;
int currentPage = DataSource.Limit == null ? 0 : DataSource.Limit.From / pageSize;
int pagesCount = DataSource.Limit == null ? 1 : (int)Math.Ceiling((decimal)totalrecordsCount / pageSize);
Content.ColumnCount = DataSource.Members.Count; //one column per member
Content.RowCount = 1; //create header first
//create header row
int column = 0;
foreach (DataMember member in DataSource.Members.Select(dm => dm.DataMember))
{
ILabelButton header = Platform.Current.Create <ILabelButton>();
header.Text = Translator.Translate(member.Member.FinalMemberInfo);
header.Click += Header_Click;
Content.SetContent(0, column, header);
column++;
}
//create data rows
var result = db.Select(DataSource);
db.Dispose();
foreach (object instance in result)
{
column = 0;
Content.RowCount++;
foreach (DataMember member in DataSource.Members.Select(dm => dm.DataMember))
{
ILabelButton content = Platform.Current.Create <ILabelButton>();
content.Text = member.Member.GetValue(instance).ToString();
content.Tag = instance;
content.Click += Content_Click;
Content.SetContent(Content.RowCount - 1, column++, content);
}
}
//create footer and pagination
if (pageSize != 0)
{
Content.RowCount++;
IGrid pagination = Platform.Current.Create <IGrid>();
pagination.RowCount = 1;
pagination.ColumnCount = 2;
//add page size picker
IListPicker pageSizeOptions = Platform.Current.Create <IListPicker>();
pageSizeOptions.Items = new List <string>();
//handle page size change
pageSizeOptions.ValueChanged += PageSizeOptions_ValueChanged;
//create 5 options for page sizes
for (int i = 1; i < 6; i++)
{
pageSizeOptions.Items.Add((pageSize * i).ToString());
}
//add current page picker
IListPicker pageNumbers = Platform.Current.Create <IListPicker>();
pageNumbers.Items = new List <string>();
//handle paging
pageNumbers.ValueChanged += PageNumbers_ValueChanged;
//add all pages
for (int i = 1; i < pagesCount; i++)
{
pageNumbers.Items.Add(i.ToString());
}
//set current page
pageNumbers.Value = currentPage.ToString();
Content.SetContent(Content.RowCount - 1, 0, pagination);
Content.SetColumnSpan(Content.ColumnCount, pagination);
}
}
