Employee Sales by Country - Aspose.Pdf Demos

Click Process to see how example generates document containing information about employee sales (by country) based on Northwind database data
You can either open the resulting PDF file into your PDF reader or save directly to your disk.
/////////////////////////////////////////////////////////////////////////
// Copyright (C) 2002-2011 Aspose Pty Ltd.  All rights reserved.
//
// This file is part of Aspose.Pdf. The source code in this file 
// is only intended as a supplement to the documentation, and is provided 
// "as is", without warranty of any kind, either expressed or implied.
/////////////////////////////////////////////////////////////////////////

using System;
using Aspose.Pdf.Generator;
using System.Globalization;
using System.Data;
using System.Data.OleDb;

namespace Aspose.Pdf.Demos
{
	/// <summary>
	/// Summary description for EmployeeSales.
	/// </summary>
	public class EmployeeSales
	{
		private OleDbCommand oleDbSelectCommand1;
		private OleDbConnection oleDbConnection;
		private OleDbDataReader resultReader;
		string path;
		CultureInfo locale = new CultureInfo("en-US");

		public EmployeeSales(string curPath)
		{
			path = curPath;
			this.oleDbConnection = new System.Data.OleDb.OleDbConnection();
			this.oleDbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + 
				"\\Database\\Northwind.mdb";
			this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
			this.oleDbSelectCommand1.Connection = this.oleDbConnection;
			this.oleDbConnection.Open();
		}
		public Aspose.Pdf.Generator.Pdf GetEmployeeSales()
		{
            Aspose.Pdf.Generator.Pdf pdf = new Aspose.Pdf.Generator.Pdf();			
			pdf.IsTruetypeFontMapCached = false;

			// If you have purchased a license,
			// Set license like this: 
			// string licenseFile = MapPath("License") + "\\Aspose.Pdf.lic"; 
			// Aspose.Pdf.License lic = new Aspose.Pdf.License();
			// lic.SetLicense(licenseFile);

			string xmlFile = path + "\\Xml\\EmployeeSales.xml";
			pdf.BindXML(xmlFile, null);

			HeaderFooter headerFooter = pdf.Sections[0].OddFooter;
            Aspose.Pdf.Generator.Text text = (Aspose.Pdf.Generator.Text)headerFooter.Paragraphs[0];
			Segment seg = text.Segments[0];
			seg.Content = "UK-" + seg.Content;
			GenerateCountry(pdf,"UK");


			/***************EmployeeSales-USA.pdf***********************			  
			Pdf pdf = new Pdf();			

			string xmlFile = path + "\\Xml\\EmployeeSales.xml";
			pdf.BindXML(xmlFile, null);

			HeaderFooter headerFooter = pdf.Sections[0].OddFooter;
			Text text = (Text)headerFooter.Paragraphs[0];
			Segment seg = text.Segments[0];
			seg.Content = "UK-" + seg.Content;
			GenerateCountry(pdf,"USA")*/

			this.oleDbConnection.Close();
			return pdf;
		}
        private void GenerateCountry(Aspose.Pdf.Generator.Pdf pdf, string country)
		{
			float countrySalesTotal = 0,personSalesTotal = 0;
			int employeeNumber = 0,orderNumber = 0,i,j;
			string queryString = "SELECT DISTINCTROW Employees.Country, Employees.LastName, " + 
				"Employees.FirstName, Orders.OrderID, " + 
				"[Order Subtotals].Subtotal AS SaleAmount FROM Employees INNER JOIN " + 
				"(Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID) " + 
				"ON Employees.EmployeeID = Orders.EmployeeID WHERE (Employees.Country = '" + country + "') " + 
				"ORDER BY Employees.LastName,Employees.FirstName";

			Section section = pdf.Sections["section1"];
            Aspose.Pdf.Generator.Text countryText = (Aspose.Pdf.Generator.Text)section.Paragraphs["country"];
			countryText.Segments[0].Content = "Country: " + country;

			this.oleDbSelectCommand1.CommandText = "SELECT Sum(SaleAmount) AS Total FROM (" + queryString + ")";
			resultReader = this.oleDbSelectCommand1.ExecuteReader();

			resultReader.Read();
			countrySalesTotal = (float)Convert.ToDouble(resultReader.GetValue(0));

			resultReader.Close();
			this.oleDbSelectCommand1.CommandText = "SELECT Count(*) AS EmployeeNumber FROM Employees " + 
				"WHERE (Employees.Country = '" + country + "') ";
			resultReader = this.oleDbSelectCommand1.ExecuteReader();

			resultReader.Read();
			employeeNumber = Convert.ToInt32(resultReader.GetValue(0));

			Employee[] employees = new Employee[employeeNumber];

			resultReader.Close();
			this.oleDbSelectCommand1.CommandText = "SELECT Employees.LastName,Employees.FirstName " + 
				"FROM Employees WHERE (Employees.Country = '" + country + 
				"') ORDER BY Employees.LastName,Employees.FirstName";

			resultReader = this.oleDbSelectCommand1.ExecuteReader();
			i = 0;
			while(resultReader.Read())
			{
				employees[i] = new Employee();
				employees[i].LastName = Convert.ToString(resultReader.GetValue(0));
				employees[i].FirstName = Convert.ToString(resultReader.GetValue(1));
				i++;
			}

			for(i = 0; i < employeeNumber; i++)
			{
				resultReader.Close();
				this.oleDbSelectCommand1.CommandText = "SELECT Sum(SaleAmount) AS Total FROM (" + queryString + ") " + 
					"WHERE Employees.LastName = '" + employees[i].LastName + "' And Employees.FirstName = '" +
					employees[i].FirstName + "'";
				resultReader = this.oleDbSelectCommand1.ExecuteReader();

				resultReader.Read();
				personSalesTotal = (float)Convert.ToDouble(resultReader.GetValue(0));

				employees[i].SalesTotal = (int)personSalesTotal;
				employees[i].PercentOfCountry = (float)((float)((int)((((float)personSalesTotal / 
					countrySalesTotal) + 0.00005) * 10000)) / 100);

				resultReader.Close();
				this.oleDbSelectCommand1.CommandText = "SELECT Count(*) AS OrderNumber FROM (" + queryString + ") " + 
					"WHERE Employees.LastName = '" + employees[i].LastName + "' And Employees.FirstName = '" +
					employees[i].FirstName + "'";

				resultReader = this.oleDbSelectCommand1.ExecuteReader();
				resultReader.Read();
				orderNumber = Convert.ToInt32(resultReader.GetValue(0));

				employees[i].OrderNumber = orderNumber;
				SalesPerson[] personSales = new SalesPerson[orderNumber];

				resultReader.Close();
				this.oleDbSelectCommand1.CommandText = "SELECT Orders.OrderID, SaleAmount FROM (" + queryString + ") " + 
					"WHERE Employees.LastName = '" + employees[i].LastName + "' And Employees.FirstName = '" +
					employees[i].FirstName + "'";
				resultReader = this.oleDbSelectCommand1.ExecuteReader();
				j = 0;
				while(resultReader.Read())
				{
					personSales[j] = new SalesPerson();
					personSales[j].OrderID = Convert.ToInt32(resultReader.GetValue(0));
					personSales[j].SalesAmount = Convert.ToInt32(resultReader.GetValue(1));
					personSales[j].PercentOfPerson = (float)((float)((int)((((float)personSales[j].SalesAmount / 
						employees[i].SalesTotal) + 0.00005) * 10000)) / 100);
					personSales[j].PercentOfCountry = (float)((float)((int)((((float)personSales[j].SalesAmount / 
						countrySalesTotal) + 0.00005) * 10000)) / 100);
					j++;
						
				}
				PutEmployee(pdf,section,employees[i],personSales,(int)countrySalesTotal);
			}

			Table amountTable  = new Table();
			amountTable.Margin.Top = 10;
			amountTable.Margin.Bottom = 10;
			amountTable.DefaultCellTextInfo.FontSize = 10;
			amountTable.ColumnWidths = "215 150";
			section.Paragraphs.Add(amountTable);
			Row row1AmountTable = amountTable.Rows.Add();

			Cell cell1Row1AmountTable = row1AmountTable.Cells.Add("Total for " + country + ":");
            ((Aspose.Pdf.Generator.Text)cell1Row1AmountTable.Paragraphs[0]).Segments[0].TextInfo.FontName = "Times-Bold";
			
			row1AmountTable.Cells.Add(countrySalesTotal.ToString("c",locale));

			Graph endGraph = new Graph(section,480,40);
			endGraph.Margin.Bottom = 20;
			section.Paragraphs.Add(endGraph);
			float[] linePosArr = new float[]{0,30,480,30};
			Line endLine = new Line(endGraph,linePosArr);
			endGraph.Shapes.Add(endLine);
            endLine.GraphInfo.Color = new Aspose.Pdf.Generator.Color(153);
		}

        private void PutEmployee(Aspose.Pdf.Generator.Pdf pdf, Section section, Employee employee, SalesPerson[] personSales,
			int countryAmount)
		{
			CultureInfo culture = new CultureInfo("en-US");

			Table headerTable = new Table();
			section.Paragraphs.Add(headerTable);
			headerTable.Margin.Left = 25;
			headerTable.ColumnWidths = "170 90";
			headerTable.DefaultCellTextInfo.FontSize = 10;
			Row row1HeaderTable = headerTable.Rows.Add();

			row1HeaderTable.Cells.Add("Sales persion: " + employee.LastName + "," + employee.FirstName);

			Cell cell2Row1HeaderTable = row1HeaderTable.Cells.Add("Exceeded Goal");
            ((Aspose.Pdf.Generator.Text)cell2Row1HeaderTable.Paragraphs[0]).Segments[0].TextInfo.Color = new Aspose.Pdf.Generator.Color("Red");

			Graph headerGraph = new Graph(section,260,10);
			section.Paragraphs.Add(headerGraph);
			float[] linePosArr = new float[]{20,5,260,5};
			Line headerLine = new Line(headerGraph,linePosArr);
			headerGraph.Shapes.Add(headerLine);
            headerLine.GraphInfo.Color = new Aspose.Pdf.Generator.Color("Red");

			Table table;
			table = AddTable(pdf,section);
			int i;
			for(i = 0 ; i < employee.OrderNumber; i++)
			{
				AddRow(section,table,personSales[i].OrderID.ToString(),
					personSales[i].SalesAmount.ToString("c",culture),
					personSales[i].PercentOfPerson.ToString() + "%",
					personSales[i].PercentOfCountry.ToString() + "%",false);
			}


			Table amountTable  = new Table();
			amountTable.Margin.Top = 10;
			amountTable.Margin.Left = 20;
			amountTable.Margin.Bottom = 10;
			section.Paragraphs.Add(amountTable);
			amountTable.DefaultCellTextInfo.FontSize = 10;
			amountTable.ColumnWidths = "195 150 70";
			Row row1AmountTable = amountTable.Rows.Add();

			Cell cell1Row1AmountTable =
				row1AmountTable.Cells.Add("Total for " + employee.LastName + employee.FirstName);
            ((Aspose.Pdf.Generator.Text)cell1Row1AmountTable.Paragraphs[0]).Segments[0].TextInfo.FontName = "Times-Bold";

			row1AmountTable.Cells.Add(employee.SalesTotal.ToString("c",culture));

			Cell cell3Row1AmountTable = row1AmountTable.Cells.Add(employee.PercentOfCountry.ToString() + "%");
            ((Aspose.Pdf.Generator.Text)cell3Row1AmountTable.Paragraphs[0]).TextInfo.Alignment = AlignmentType.Right;

			Graph endGraph = new Graph(section,460,40);
			endGraph.Margin.Bottom = 20;
			endGraph.Margin.Left = 20;
			section.Paragraphs.Add(endGraph);
			linePosArr = new float[]{0,30,460,30};
			Line endLine = new Line(endGraph,linePosArr);
			endGraph.Shapes.Add(endLine);
            endLine.GraphInfo.Color = new Aspose.Pdf.Generator.Color(153);
		}

        private Table AddTable(Aspose.Pdf.Generator.Pdf pdf, Section section)
		{
			Table table1 = new Table();
			table1.ColumnWidths = "100 80 120 100";
			table1.DefaultCellTextInfo.FontSize = 10;
			table1.DefaultCellPadding.Top = 3;
			table1.DefaultCellPadding.Bottom = 3;
			table1.Margin.Left = 70;
			table1.Margin.Bottom = 20;
			section.Paragraphs.Add(table1);
			AddRow(section,table1,"Order ID:","Sale Amount:","Percent of Sales Person's:",
				"Percent of Country Total",true);

			return table1;
		}
		private void AddRow(Section section,Table table,string s1,string s2,string s3,string s4,bool isHeader)
		{
			Row row2 = table.Rows.Add();
			if(isHeader)
                row2.Border = new BorderInfo((int)(BorderSide.Top | BorderSide.Bottom), new Aspose.Pdf.Generator.Color(153));

			Cell row2Cell1 = row2.Cells.Add(s1);
            ((Aspose.Pdf.Generator.Text)row2Cell1.Paragraphs[0]).TextInfo.Alignment = AlignmentType.Center;

			Cell row2Cell2 = row2.Cells.Add(s2);
            ((Aspose.Pdf.Generator.Text)row2Cell2.Paragraphs[0]).TextInfo.Alignment = AlignmentType.Right;

			Cell row2Cell3 = row2.Cells.Add(s3);
            ((Aspose.Pdf.Generator.Text)row2Cell3.Paragraphs[0]).TextInfo.Alignment = AlignmentType.Center;

			Cell row2Cell4 = row2.Cells.Add(s4);
            ((Aspose.Pdf.Generator.Text)row2Cell4.Paragraphs[0]).TextInfo.Alignment = AlignmentType.Center;
		}

		class SalesPerson
		{
			public SalesPerson()
			{
			}
			private int orderID;
			private int salesAmount;
			private float percentOfPerson;
			private float percentOfCountry;
			public int OrderID
			{
				get
				{
					return orderID;
				}
				set
				{
					orderID = value;
				}
			}
			public int SalesAmount
			{
				get
				{
					return salesAmount;
				}
				set
				{
					salesAmount = value;
				}
			}
			public float PercentOfPerson
			{
				get
				{
					return percentOfPerson;
				}
				set
				{
					percentOfPerson = value;
				}
			}
			public float PercentOfCountry
			{
				get
				{
					return percentOfCountry;
				}
				set
				{
					percentOfCountry = value;
				}
			}
		}
		class Employee
		{
			public Employee()
			{
				salesTotal = 0;
				orderNumber = 0;
			}
			private string lastName;
			private string firstName;
			private int salesTotal;
			private int orderNumber;
			private float percentOfCountry;
			public string LastName
			{
				get
				{
					return lastName;
				}
				set
				{
					lastName = value;
				}
			}
			public string FirstName
			{
				get
				{
					return firstName;
				}
				set
				{
					firstName = value;
				}
			}
			public int SalesTotal
			{
				get
				{
					return salesTotal;
				}
				set
				{
					salesTotal = value;
				}
			}
			public int OrderNumber
			{
				get
				{
					return orderNumber;
				}
				set
				{
					orderNumber = value;
				}
			}
			public float PercentOfCountry
			{
				get
				{
					return percentOfCountry;
				}
				set
				{
					percentOfCountry = value;
				}
			}
		}
	}
}