public static DataTable Aggregate(EnumerableRowCollection <DataRow> resu, Criteria currentCriteria)
{
IEnumerable <string> columnsToGroupBy = currentCriteria.agKey.Split(',');
IEnumerable <string> sumsToSelect = currentCriteria.agSum.Split(',');
var keys = currentCriteria.agKey.Split(',');
var groupList = resu.GroupBy(x => new NTuple <object>(from column in columnsToGroupBy select x[column])); //.Select(val => new { nK=val.FirstOrDefault().Field<string>(keys[0])+"~"+ val.FirstOrDefault().Field<string>(keys[1]), total=val.Sum(c=>Convert.ToDecimal(c.Field<string>(sumsToSelect.FirstOrDefault()))).ToString()});//new NTuple<object>(from sum in sumsToSelect select val[sum])
if (resu.Count() == 0)
{
return(new DataTable());
}
DataTable aggregatedTable = resu.FirstOrDefault().Table.Clone();
aggregatedTable.Columns.Add("AggregatedKey", typeof(string));
/*foreach (DataColumn col in resu.ElementAtOrDefault(0).Table.Columns)
* {
* table.Columns.Add(col.ColumnName, col.DataType);
* }*/
//DataRow toAdd = aggregatedTable.NewRow();
//toAdd.Table.Columns.Add("AggregatedKey", typeof(string));
//toAdd["AggregatedKey"] = aggregatedKey;
foreach (var group in groupList)
{
string aggregatedKey = "";
DataRow toAdd = group.FirstOrDefault();
if (!(toAdd.Table.Columns.Contains("AggregatedKey")))
{
toAdd.Table.Columns.Add("AggregatedKey", typeof(string));
}
for (int i = 1; i < group.Count(); i++)
{ //each row in the group (except first)
for (int j = 0; j < group.ElementAt(i).Table.Columns.Count; j++) //each column in row
{
var colName = group.ElementAt(i).Table.Columns[j].ColumnName;
if (sumsToSelect.Contains(colName))
{
toAdd[colName] = Convert.ToDouble(toAdd[colName].ToString()) + Convert.ToDouble(group.ElementAt(i)[colName].ToString());
}
else if (toAdd[colName].ToString() != group.ElementAt(i)[colName].ToString())
{
toAdd[colName] = null;//group.ElementAt(i)[colName].ToString();
}
}
}
for (int i = 0; i < columnsToGroupBy.Count(); i++)
{
if (i == 0)
{
aggregatedKey = toAdd[columnsToGroupBy.ElementAt(i)].ToString();
}
else
{
aggregatedKey += "~" + toAdd[columnsToGroupBy.ElementAt(i)].ToString();
}
}
toAdd["AggregatedKey"] = aggregatedKey;
aggregatedTable.Rows.Add(toAdd.ItemArray);
}
return(aggregatedTable);
}
public static void ParseCriteria(string path)
{
var client = new DocumentClient(new Uri(EndpointUrl), PrimaryKey);
string line;
int colCounter = 0;
int entrieCount = 0;
string agKey = "";
string agSum = "";
Criteria criteria = new Criteria();
Pre pre = new Pre();
Post post = new Post();
int index = 0;
System.IO.StreamReader file = new System.IO.StreamReader(path);
while ((line = file.ReadLine()) != null)
{
if (!line.StartsWith("--"))
{
if (line.StartsWith("!"))
{
criteria = new Criteria();
pre = new Pre();
post = new Post();
criteria.Name = line.Substring(1);
colCounter = 0;
entrieCount = 0;
agKey = "";
agSum = "";
}
else if (line.StartsWith("@"))
{
if (criteria.pre == null)
{
criteria.pre = new List <Pre>();
}
if (pre.column != null)
{
criteria.pre.Add(pre);
}
pre = new Pre();
pre.column = line.Substring(1);
}
else if (line.StartsWith("^"))
{
pre.process = line.Substring(1);
}
else if (line.StartsWith("#"))
{
if (Regex.IsMatch(line, @"[A-Za-z]"))
{
if (pre.values == null)
{
pre.values = new List <string>();
}
pre.values.Add(line.Substring(1));
}
if (Regex.IsMatch(line, @"\d"))
{
if (post.values == null)
{
post.values = new List <string>();
}
post.values.Add(line.Substring(1));
}
}
else if (line.StartsWith("*"))
{
agKey += line.Substring(1);
agKey += ",";
}
else if (line.StartsWith("+"))
{
agSum += line.Substring(1);
agSum += ",";
entrieCount = 0;
}
else if (line.StartsWith("$"))
{
if (criteria.post == null)
{
criteria.post = new List <Post>();
}
if (post.column != null)
{
criteria.post.Add(post);
}
post = new Post();
post.column = line.Substring(1);
}
else if (line.StartsWith("&"))
{
post.process = line.Substring(1);
}
if (file.EndOfStream || (string.IsNullOrEmpty(line) && (!string.IsNullOrEmpty(criteria.Name))))
{
criteria.pre.Add(pre);
criteria.agKey = agKey.Substring(0, agKey.Length - 1);
criteria.agSum = agSum.Substring(0, agSum.Length - 1);
criteria.post.Add(post);
try
{
var cb = new SqlConnectionStringBuilder();
cb.DataSource = "tcp:cis625.database.windows.net,1433";
cb.UserID = "admin123";
cb.Password = "******";
cb.InitialCatalog = "625data";
var propertiesOfUser = client.CreateDocumentQuery <Criteria>(UriFactory.CreateDocumentCollectionUri("criteria", "criteriaSets"))
.Where(p => p.Name == criteria.Name)
.ToList();
if (!(propertiesOfUser.Count > 0))
{
using (SqlConnection dbConnection = new SqlConnection(cb.ConnectionString))
{
dbConnection.Open();
string query = string.Format("INSERT INTO CriteriaFiltering.CriteriaSets(Name) Values('{0}')", criteria.Name);
SqlCommand cmd = new SqlCommand(query, dbConnection);
cmd.ExecuteNonQuery();
dbConnection.Close();
}
Document doc = client.CreateDocumentAsync(UriFactory.CreateDocumentCollectionUri("criteria", "criteriaSets"), criteria).Result;
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
}
}