Esempio n. 1
0
        protected string BuildJoinQuery(DateTime start, DateTime end, GroupingPeriod period, GroupColumn column) {
            string groupBy = GetGroupBy(period, "Sales.Sale_Date");
            string groupByField = string.Empty;
            string groupByCondition = string.Empty;
            if(!string.IsNullOrEmpty(groupBy)) {
                groupByField = ", " + groupBy + " AS SaleDate";
                groupByCondition = ", " + groupBy;
            }
            string keyColumn = string.Empty;
            string tableName = string.Empty;
            string columnName = string.Empty;
            if(column != GroupColumn.None) {
                string name = Enum.GetName(typeof(GroupColumn), column);
                keyColumn = name + "Id";
                tableName = name + "s";
                columnName = name + "_Name";
            }
            string query = string.Format(@"SELECT
	                                        {1} as Name,
	                                        SUM(Sales.Total_Cost) as TotalCost, 
	                                        SUM(Sales.Units) as Units
                                            {2}
                                        FROM 
                                            Sales
                                        INNER JOIN 
                                            {3} as LinkedTable on Sales.{0} = LinkedTable.Id
                                        WHERE 
                                            Sales.Sale_Date BETWEEN @PeriodStart and @PeriodEnd  
                                        GROUP BY
                                            {1} {4}", keyColumn, Cstr("LinkedTable." + columnName), groupByField, tableName, groupByCondition);
            return query;
        }
Esempio n. 2
0
		public void GroupbyOneColumnSortSubList () {
			var rs = new ReportSettings();
			var gc = new GroupColumn() {
				ColumnName ="GroupItem",
				SortDirection = ListSortDirection.Ascending,
				GroupSortColumn = new SortColumn() {
					ColumnName = "Randomint",
					SortDirection = ListSortDirection.Ascending
				}
			};
			rs.GroupColumnsCollection.Add(gc);
			var collectionSource = new CollectionDataSource	(list,rs);
			collectionSource.Bind();
			var testKey = String.Empty;
			var testSubKey = -1;
			var groupedList = collectionSource.GroupedList;
			foreach (var element in groupedList) {
				Assert.That(element.Key,Is.GreaterThan(testKey));
				testKey = element.Key.ToString();
				foreach (Contributor sub in element) {
					Assert.That(sub.RandomInt,Is.GreaterThanOrEqualTo(testSubKey));
					testSubKey = sub.RandomInt;
				}
				testSubKey = -1;
			}
		}
Esempio n. 3
0
		public void AddGroupColumn ()
		{
			GroupColumn gc = new GroupColumn("GroupItem",1,ListSortDirection.Ascending);
			ReportSettings rs = new ReportSettings();
			
			rs.GroupColumnsCollection.Add(gc);
			Assert.AreEqual(1,rs.GroupColumnsCollection.Count);
		}
		public void Constructor()
		{
			GroupColumn gc = new GroupColumn ("TestColumn",1, ListSortDirection.Ascending);
			Assert.AreEqual(columnName,gc.ColumnName);
			Assert.AreEqual(1,gc.GroupLevel);
			Assert.AreEqual(ListSortDirection.Ascending,gc.SortDirection);
			Type t = typeof(System.String);
			Assert.AreEqual(t,gc.DataType);
		}
		public void CanGroup_All_Elements_Contains_Children ()
		{
			GroupColumn groupComparer = new GroupColumn("GroupItem",1,ListSortDirection.Ascending);
			var  tableStrategy =GroupTableStrategyFactory (groupComparer);
			tableStrategy.Bind();
			foreach (BaseComparer element in tableStrategy.IndexList) 
			{
				GroupComparer checkComparer = element as GroupComparer;
				Assert.That(checkComparer.IndexList.Count > 0);
			}
		}
		public void CanGroup_All_Elements_are_GroupComparer ()
		{
			GroupColumn groupComparer = new GroupColumn("GroupItem",1,ListSortDirection.Ascending);
			ICSharpCode.Reports.Core.ListStrategy.TableStrategy tableStrategy = GroupTableStrategyFactory (groupComparer);
			tableStrategy.Bind();
			foreach (BaseComparer element in tableStrategy.IndexList) 
			{
				GroupComparer checkComparer = element as GroupComparer;
				Assert.IsNotNull(checkComparer);
			}
		}
		private IDataNavigator PrepareStringGrouping ()
		{
			GroupColumn gc = new GroupColumn("GroupItem",1,ListSortDirection.Ascending);
			ReportSettings rs = new ReportSettings();
			rs.GroupColumnsCollection.Add(gc);
			IDataManager dm = ICSharpCode.Reports.Core.DataManager.CreateInstance(this.contributorCollection,rs);
			return dm.GetNavigator;
		}
Esempio n. 8
0
		private IDataNavigator PrepareDateTimeGrouping ()
		{
			Console.WriteLine("PrepareDateTimeGrouping ()");
			
			GroupColumn gc = new GroupColumn("RandomDate",1,ListSortDirection.Ascending);
			ReportSettings rs = new ReportSettings();
			rs.GroupColumnsCollection.Add(gc);
			IDataManager dm = ICSharpCode.Reports.Core.DataManager.CreateInstance(this.table,rs);
			return dm.GetNavigator;
		}
		public void ConstructorWithInvalidGroupLevel()
		{
			GroupColumn gc = new GroupColumn ("TestColumn",-1, ListSortDirection.Ascending);
		}
Esempio n. 10
0
		public void SortChildrenDescending()
		{
			ReportSettings rs = new ReportSettings();
			GroupColumn gc = new GroupColumn("GroupItem",1,ListSortDirection.Ascending);
			rs.GroupColumnsCollection.Add(gc);
			
			SortColumn sc = new SortColumn("Last",ListSortDirection.Descending);
			rs.SortColumnsCollection.Add(sc);
			IDataManager dm = ICSharpCode.Reports.Core.DataManager.CreateInstance(this.table,rs);
			var dataNavigator = dm.GetNavigator;
			
			string compare = String.Empty;
			
			while (dataNavigator.MoveNext())
			{
				DataRow dr = dataNavigator.Current as DataRow;
				var result = dr[3].ToString();
				Assert.That(compare,Is.LessThan(result));
				if (dataNavigator.HasChildren)
				{
					string compareChild = String.Empty;
					var childNavigator = dataNavigator.GetChildNavigator;
					do
					{
						DataRow childRow = childNavigator.Current as DataRow;
						var childResult = childRow[1].ToString();
						if (!String.IsNullOrEmpty(compareChild)) {
							Assert.LessOrEqual(childResult,compareChild);
						}
//						Console.WriteLine("\t{0}",childResult);
						compareChild = childResult;
					}
					while (childNavigator.MoveNext());
				}
				compare = result;
			}
		}
Esempio n. 11
0
		private ICSharpCode.Reports.Core.ListStrategy.TableStrategy GroupTableStrategyFactory (GroupColumn sortColumn)
		{
			var reportSettings = new ReportSettings();
			reportSettings.GroupColumnsCollection.Add(sortColumn);
			var tableStrategy = new ICSharpCode.Reports.Core.ListStrategy.TableStrategy(this.table,reportSettings);
			return tableStrategy;
		}
Esempio n. 12
0
 protected virtual DbCommand CreateJoinQuery(DateTime start, DateTime end, GroupingPeriod period, GroupColumn column) {
     string query = BuildJoinQuery(start, end, period, column);
     OleDbCommand command = new OleDbCommand(query);
     command.Parameters.Add(new OleDbParameter("@PeriodStart", OleDbType.Date)).Value = start;
     command.Parameters.Add(new OleDbParameter("@PeriodEnd", OleDbType.Date)).Value = end;
     return command;
 }
Esempio n. 13
0
        IEnumerable<SalesGroup> ExecuteQuery(DateTime start, DateTime end, GroupingPeriod period, GroupColumn column) {
            if(start < MinDate) 
                start = MinDate;
            if(end > MaxDate) 
                end = MaxDate;
            IList<SalesGroup> result = null;
            if(connection.State != ConnectionState.Open)
                connection.Open();
            DbCommand command;
            if(column == GroupColumn.None)
                command = CreateQueryBySales(start, end, period);
            else
                command = CreateJoinQuery(start, end, period, column);
            command.Connection = connection;
            command.Prepare();

            result = new List<SalesGroup>();
            using(var reader = command.ExecuteReader()) {
                while(reader.Read()) {
                    string name = reader["Name"].ToString();
                    decimal totalCost = 0;
                    int units = 0;
                    DateTime startDate = start;
                    DateTime endDate = end;
                    if(!(reader["TotalCost"] is DBNull)) {
                        totalCost = Convert.ToDecimal(reader["TotalCost"]);
                        units = Convert.ToInt32(reader["Units"]);
                        if(reader.FieldCount > 3) {
                            startDate = Convert.ToDateTime(reader["SaleDate"]);
                            endDate = GetEndDate(startDate, end, period);
                        }
                    }
                    result.Add(new SalesGroup(name, totalCost, units, startDate, endDate));
                }
            }
            return result;
        }
Esempio n. 14
0
		public void SortChildrenDescending()
		{
			ReportSettings rs = new ReportSettings();
			GroupColumn gc = new GroupColumn("GroupItem",1,ListSortDirection.Ascending);
			rs.GroupColumnsCollection.Add(gc);
			
			SortColumn sc = new SortColumn("Last",ListSortDirection.Descending);
			rs.SortColumnsCollection.Add(sc);
			IDataManager dm = ICSharpCode.Reports.Core.DataManager.CreateInstance(this.contributorCollection,rs);
			var dataNavigator = dm.GetNavigator;
			
			ReportItemCollection searchCol = new ReportItemCollection();
			searchCol.Add(new BaseDataItem ()
			              {
			              	Name ="Last",
			              	ColumnName ="Last"
			              }
			             );
			
			searchCol.Add(new BaseDataItem ()
			              {
			              	
			              	ColumnName ="GroupItem"
			              }
			             );
			
			string compare = String.Empty;

			while (dataNavigator.MoveNext())
			{
				dataNavigator.Fill(searchCol);
				var column = (BaseDataItem)searchCol[1];
				var result = column.DBValue.ToString();
				
				Assert.That (compare,Is.LessThan(result));
				if (dataNavigator.HasChildren) {
					string compareChild = String.Empty;
					var childNavigator = dataNavigator.GetChildNavigator;
					do
					{
						childNavigator.Fill(searchCol);
						var childColumn = (BaseDataItem)searchCol[0];
						var childResult = childColumn.DBValue.ToString();
//						Console.WriteLine("\t{0}",childResult);
						if (!String.IsNullOrEmpty(compareChild)) {
							Assert.LessOrEqual(childResult,compareChild);
						}
						compareChild = childResult;
					}
					while (childNavigator.MoveNext());
				}
			}
		}
Esempio n. 15
0
		public void CollectionContainsSubclass ()
		{
			var modifyedCollection = this.ModifyCollection();
			GroupColumn gc = new GroupColumn("GroupItem",1,ListSortDirection.Ascending);
			ReportSettings rs = new ReportSettings();
			rs.GroupColumnsCollection.Add(gc);
			
			IDataManager dm = ICSharpCode.Reports.Core.DataManager.CreateInstance(modifyedCollection,rs);
			IDataNavigator dataNavigator = dm.GetNavigator;
			
			ReportItemCollection searchCol = new ReportItemCollection();
			searchCol.Add(new BaseDataItem ()
			              {
			              	ColumnName ="DummyClass.DummyString"			     
			              }
			             );
			searchCol.Add(new BaseDataItem ()
			              {
			              	Name ="Last",			           
			              	ColumnName ="Last"	
			              }
			             );
			
			searchCol.Add(new BaseDataItem ()
			              {
			             			           
			              	ColumnName ="GroupItem"	
			              }
			             );
			
			string compare = string.Empty;

			while (dataNavigator.MoveNext())
			{
				dataNavigator.Fill(searchCol);
				var b1 = (BaseDataItem)searchCol[2];
				var result = b1.DBValue;				
				Assert.That (compare,Is.LessThan(result));
				
				if (dataNavigator.HasChildren)
				{
					var childNavigator = dataNavigator.GetChildNavigator;
					do
					{
						childNavigator.Fill(searchCol);
						var itemDummy = (BaseDataItem)searchCol[0];
						var itemLast = (BaseDataItem)searchCol[1];
						var itemGroup = (BaseDataItem)searchCol[2];
						Console.WriteLine ("\t{0} - {1} - {2}",itemDummy.DBValue,itemLast.DBValue,itemGroup.DBValue);
						Assert.That(itemDummy.DBValue,Is.Not.Empty);
						Assert.That(itemLast.DBValue,Is.Not.Empty);
						Assert.That(itemGroup.DBValue,Is.Not.Empty);
					}
					while (childNavigator.MoveNext());
				}
				compare = result;
			}
		}