private int GetSortColumnIndex()
{
// Iterate through the Columns collection to determine the index of the column being sorted.
foreach (DataControlField field in gvImportjob.Columns)
{
if (field.SortExpression != "" && field.SortExpression != null)
{
string sortexp = "";
if (SortExpression.Contains("asc"))
{
sortexp = SortExpression.Replace("asc", "");
}
else if (SortExpression.Contains("desc"))
{
sortexp = SortExpression.Replace("desc", "");
}
if (field.SortExpression.ToString().ToLower() == sortexp.ToLower().Trim())
{
return(gvImportjob.Columns.IndexOf(field));
}
}
}
return(-1);
}
/// <summary>
/// Get Sort Expression by Looking up the existing Grid View Sort Expression
/// </summary>
protected string GetSortExpression(GridViewSortEventArgs e)
{
if (!this.DesignMode)
{
string[] sortColumns = null;
string sortAttribute = string.Empty;
//if (this.SortExpression.Trim() != "")
//{
sortAttribute = SortExpression;
//}
//else
//{
// sortAttribute = this.DefaultSortExpression;
//}
if (e.SortExpression != "" && this.DefaultSortExpression != "")
{
sortAttribute = SortExpression.Replace(this.DefaultSortExpression, "");
}
//Check to See if we have an existing Sort Order already in the Grid View.
//If so get the Sort Columns into an array
if (sortAttribute != String.Empty)
{
sortColumns = sortAttribute.Split(",".ToCharArray());
}
//if User clicked on the columns in the existing sort sequence.
//Toggle the sort order or remove the column from sort appropriately
if (sortAttribute.IndexOf(e.SortExpression) > 0 || sortAttribute.StartsWith(e.SortExpression))
{
sortAttribute = ModifySortExpression(sortColumns, e.SortExpression);
}
else
{
sortAttribute += String.Concat(",", e.SortExpression, " ASC ");
}
return(sortAttribute.TrimStart(",".ToCharArray()).TrimEnd(",".ToCharArray()));
}
else
{
return("");
}
}
private string Aggregate()
{
if (_loadOption.RequireTotalCount || _loadOption.TotalSummary?.Any() == true ||
_loadOption.Group?.Any() == true)
{
var sb = new StringBuilder();
sb.AppendLine("COLLECT");
if (_loadOption.Group?.Any() == true)
{
var groups = _loadOption.Group.Where(x => x.GroupInterval != "hour" && x.GroupInterval != "minute")
.Select(g =>
{
var selector = _settings.ValidPropertyName(g.Selector).FirstCharOfPropertiesToUpper();
var selectorRight = selector;
var selectorLeft = selector.Replace(".", "");
if (g.GroupInterval == "year")
{
Groups.Add($"YEAR{selectorLeft}");
return($"YEAR{selectorLeft} = DATE_YEAR({_settings.IteratorVar}.{selectorRight})");
}
if (g.GroupInterval == "month")
{
Groups.Add($"MONTH{selectorLeft}");
return($"MONTH{selectorLeft} = DATE_MONTH({_settings.IteratorVar}.{selectorRight})");
}
if (g.GroupInterval == "day")
{
Groups.Add($"DAY{selectorLeft}");
return($"DAY{selectorLeft} = DATE_DAY({_settings.IteratorVar}.{selectorRight})");
}
Groups.Add(selectorLeft);
return($"{selectorLeft} = {_settings.IteratorVar}.{selectorRight}");
}).ToList();
sb.AppendLine(string.Join(", ", groups));
}
var aggregates = new List <string>
{
"TotalCount = LENGTH(1)"
};
if (_loadOption.Group?.Any() == true && _loadOption.GroupSummary?.Any() == true)
{
aggregates.AddRange(_loadOption.GroupSummary.Select(s =>
{
var selector = _settings.ValidPropertyName(s.Selector).FirstCharOfPropertiesToUpper();
var rightSelector = selector;
var leftSelector = selector.Replace(".", "");
var op = s.SummaryType.ToUpperInvariant();
Summaries.Add($"{op}{leftSelector}");
if (op == "SUM" || op == "AVG" || op == "MIN" || op == "MAX" || op == "COUNT")
{
return($"{op}{leftSelector} = {op}({_settings.IteratorVar}.{rightSelector})");
}
return($"{op}{leftSelector} = SUM(0)");
}));
}
else if (_loadOption.TotalSummary?.Any() == true)
{
aggregates.AddRange(_loadOption.TotalSummary.Select(s =>
{
var selector = _settings.ValidPropertyName(s.Selector).FirstCharOfPropertiesToUpper();
var rightSelector = selector;
var leftSelector = selector.Replace(".", "");
var op = s.SummaryType.ToUpperInvariant();
Summaries.Add($"{op}{leftSelector}");
if (op == "SUM" || op == "AVG" || op == "MIN" || op == "MAX" || op == "COUNT")
{
return($"{op}{leftSelector} = {op}({_settings.IteratorVar}.{rightSelector})");
}
return($"{op}{leftSelector} = SUM(0)");
}));
}
sb.AppendLine("AGGREGATE");
sb.AppendLine(string.Join(", ", aggregates));
var projection = new List <string> {
"TotalCount"
};
foreach (var group in Groups)
{
projection.Add(group);
}
if (_settings.GroupLookups?.Any() == true)
{
foreach (var glookup in _settings.GroupLookups)
{
var g = Groups.SingleOrDefault(x =>
x.Equals(glookup.Key, StringComparison.InvariantCultureIgnoreCase));
if (g != null)
{
projection.Add($"{g}_DV: {glookup.Value}");
}
}
}
foreach (var summary in Summaries)
{
projection.Add(summary);
}
if (_loadOption.Group?.Any() == true)
{
sb.AppendLine(SortExpression.Replace(".", "")); // Refactor
}
sb.AppendLine("RETURN {");
sb.AppendLine(string.Join(", ", projection));
sb.AppendLine("}");
return(sb.ToString());
}
return(string.Empty);
}
