Beispiel #1
0
        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);
        }
Beispiel #2
0
        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);
                        }
                    }
                }
            }
        }