using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Windows.Forms;
namespace TESTWindowsApps
{
public partial class Winform : Form
{
static string
connString = ConfigurationManager.AppSettings["connString"];
DataTable results = new DataTable();
DataTable TESTSummary = new DataTable();
string code;string
startdate;string toDate;
public string
ProcessCode
{
get { return code; }
set { code = value; }
}
public string
StartDate
{
get { return
startdate; }
set { startdate = value; }
}
public string ToDate
{
get { return toDate;
}
set { toDate = value; }
}
public Winform()
{
InitializeComponent();
}
private void
Winform_Load(object sender, EventArgs e)
{
try
{
results = new DataTable();
using (OleDbConnection conn = new OleDbConnection(connString))
{
conn.Open();
DataTable TESTSummary = getTESTSummaryAllData(conn,
"SELECT * FROM TESTSummaryQuery", ProcessCode, Convert.ToDateTime(StartDate).ToString("dd/MM/yyyy"), Convert.ToDateTime(ToDate).ToString("dd/MM/yyyy"));
lblCell.Text = "IS";
lblProcess.Text = "Test Service Request";
//Advanced Pivot
Pivote advPivot = new Pivote(TESTSummary);
HtmlTable advancedPivot =
advPivot.PivotTable("OperationCode", "TESTSummaryQuery.YearWeek", new string[] { "TB", "Percentage", "Output" });
StringWriter sw1 = new StringWriter();
HtmlTextWriter hw1 = new HtmlTextWriter(sw1);
advancedPivot.RenderControl(hw1);
StringReader sr1 = new StringReader(sw1.ToString());
string aLine, aParagraph = null;
while (true)
{
aLine = sr1.ReadLine();
if (aLine != null)
{
aParagraph =
aParagraph + aLine + " ";
}
else
{
aParagraph = aParagraph + "\n";
break;
}
}
System.IO.File.WriteAllText(@"c:\TEST\Source\HTML\Pivot.HTML", aParagraph.ToString());
this.webBrowser1.Navigate("file:///c:/TEST/Source/HTML/Pivot.HTML");
results= TESTSummary.DefaultView.ToTable(true, "OperationCode", "OperationName");
dataGridView1.DataSource =
results;
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
MessageBox.Show(msg);
}
}
private DataTable getTESTSummaryAllData(OleDbConnection conn, string Qry, string process, string startdate, string enddate)
{
results = new DataTable();
using (OleDbCommand cmd = new OleDbCommand(Qry, conn))
{
cmd.Parameters.Add("@Process", OleDbType.VarChar).Value = process;
cmd.Parameters["@Process"].Value =
process;
cmd.Parameters.Add("@StartDate", OleDbType.VarChar).Value = startdate;
cmd.Parameters["@StartDate"].Value =
startdate;
cmd.Parameters.Add("@EndDate", OleDbType.VarChar).Value = enddate;
cmd.Parameters["@EndDate"].Value =
enddate;
using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
{
adapter.Fill(results);
}
}
// return results.DefaultView.ToTable(true, "TESTSummaryQuery.OperationCode",
"OperationName");
return results;
}
private void
btnGoBack_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
/// <summary>
/// Create simple and advanced pivot reports.
/// </summary>
public class Pivot
{
#region Variables
private DataTable _DataTable;
private string
_CssTopHeading;
private string
_CssSubHeading;
private string
_CssLeftColumn;
private string
_CssItems;
private string
_CssTotals;
private string
_CssTable;
#endregion Variables
#region Constructors
public Pivot(DataTable dataTable)
{
Init();
_DataTable = dataTable;
}
#endregion Constructors
#region Properties
public DataTable ResultTable
{
get { return
_DataTable; }
}
public string
CssTopHeading
{
get { return
_CssTopHeading; }
set { _CssTopHeading = value; }
}
public string
CssSubHeading
{
get { return
_CssSubHeading; }
set { _CssSubHeading = value; }
}
public string
CssLeftColumn
{
get { return
_CssLeftColumn; }
set { _CssLeftColumn = value; }
}
public string CssItems
{
get { return
_CssItems; }
set { _CssItems = value; }
}
public string
CssTotals
{
get { return
_CssTotals; }
set { _CssTotals = value; }
}
public string CssTable
{
get { return
_CssTable; }
set { _CssTable = value; }
}
#endregion Properties
#region Private Methods
private string[]
FindValues(string xAxisField, string xAxisValue, string yAxisField, string yAxisValue, string[] zAxisFields)
{
int zAxis = zAxisFields.Length;
if (zAxis < 1)
zAxis++;
string[] zAxisValues = new string[zAxis];
//set default
values
for (int i = 0; i
<= zAxisValues.GetUpperBound(0); i++)
{
zAxisValues[i] = "0";
}
try
{
foreach (DataRow row in
_DataTable.Rows)
{
if (Convert.ToString(row[xAxisField]) == xAxisValue && Convert.ToString(row[yAxisField]) ==
yAxisValue)
{
for (int z = 0; z
< zAxis; z++)
{
zAxisValues[z] = Convert.ToString(row[zAxisFields[z]]);
}
break;
}
}
}
catch
{
throw;
}
return zAxisValues;
}
private string
FindValue(string xAxisField, string xAxisValue, string yAxisField, string yAxisValue, string zAxisField)
{
string zAxisValue = "";
try
{
foreach (DataRow row in
_DataTable.Rows)
{
if (Convert.ToString(row[xAxisField]) == xAxisValue && Convert.ToString(row[yAxisField]) ==
yAxisValue)
{
zAxisValue = Convert.ToString(row[zAxisField]);
break;
}
}
}
catch
{
throw;
}
return zAxisValue;
}
private void Init()
{
_CssTopHeading = "";
_CssSubHeading = "";
_CssLeftColumn = "";
_CssItems = "";
_CssTotals = "";
_CssTable = "";
}
private void
TableStyle(HtmlTable table)
{
if (_CssTable == "")
{
table.Style.Add("border-style", "solid");
table.Style.Add("border-width", "1px");
table.Style.Add("border-color", "Gray");
}
else
table.Attributes.Add("Class", _CssTable);
}
private void
MainHeaderTopCellStyle(HtmlTableCell cell)
{
if (_CssTopHeading == "")
{
cell.Style.Add("font-family", "tahoma");
cell.Style.Add("font-size", "10pt");
cell.Style.Add("font-weight", "normal");
cell.Style.Add("background-color", "Gray");
cell.Style.Add("color", "white");
cell.Style.Add("text-align", "center");
}
else
cell.Attributes.Add("Class", _CssTopHeading);
}
private void
MainHeaderLeftCellStyle(HtmlTableCell cell)
{
if (_CssLeftColumn == "")
{
cell.Style.Add("font-family", "tahoma");
cell.Style.Add("font-size", "10pt");
cell.Style.Add("font-weight", "normal");
cell.Style.Add("background-color", "Gray");
cell.Style.Add("color", "white");
}
else
cell.Attributes.Add("Class", _CssLeftColumn);
}
private void
SubHeaderCellStyle(HtmlTableCell cell)
{
if (_CssSubHeading == "")
{
cell.Style.Add("font-family", "tahoma");
cell.Style.Add("font-size", "10pt");
cell.Style.Add("font-weight", "normal");
cell.Style.Add("background-color", "white");
cell.Style.Add("color", "black");
}
else
cell.Attributes.Add("Class", _CssSubHeading);
}
private void
ItemCellStyle(HtmlTableCell cell)
{
if (_CssItems == "")
{
cell.Style.Add("font-family", "tahoma");
cell.Style.Add("font-size", "8pt");
cell.Style.Add("font-weight", "normal");
cell.Style.Add("background-color", "#dde4f0");
cell.Style.Add("width", "100px");
cell.Style.Add("color", "Gray");
}
else
cell.Attributes.Add("Class", _CssItems);
}
private void
TotalCellStyle(HtmlTableCell cell)
{
if (_CssTotals == "")
{
cell.Style.Add("font-family", "tahoma");
cell.Style.Add("font-size", "10pt");
cell.Style.Add("background-color", "Gray");
cell.Style.Add("color", "white");
}
else
cell.Attributes.Add("Class", _CssTotals);
}
#endregion Private Methods
#region Public Methods
/// <summary>
/// Creates an advanced 3D Pivot table.
/// </summary>
/// <param name="xAxisField">The main heading at the top of the report.</param>
/// <param name="yAxisField">The heading on the left of the report.</param>
/// <param name="zAxisFields">The sub heading at the top of the report.</param>
/// <returns>HtmlTable Control.</returns>
public HtmlTable PivotTable(string
xAxisField, string
yAxisField, string[]
zAxisFields)
{
HtmlTable table = new HtmlTable();
//style table
TableStyle(table);
/*
* The x-axis is the main horizontal row.
* The z-axis is the sub horizontal row.
* The y-axis is the left vertical column.
*/
try
{
//get
distinct xAxisFields
ArrayList xAxis = new ArrayList();
foreach (DataRow row in
_DataTable.Rows)
{
if (!xAxis.Contains(row[xAxisField]))
xAxis.Add(row[xAxisField]);
}
//get
distinct yAxisFields
ArrayList yAxis = new ArrayList();
foreach (DataRow row in
_DataTable.Rows)
{
if (!yAxis.Contains(row[yAxisField]))
yAxis.Add(row[yAxisField]);
}
//create
a 2D array for the y-axis/z-axis fields
int zAxis = zAxisFields.Length;
if (zAxis < 1)
zAxis = 1;
string[,] matrix = new string[(xAxis.Count * zAxis),
yAxis.Count];
string[] zAxisValues = new string[zAxis];
for (int y = 0; y
< yAxis.Count; y++) //loop thru y-axis fields
{
//rows
for (int x = 0; x < xAxis.Count; x++) //loop
thru x-axis fields
{
//main columns
//get the z-axis values
zAxisValues =
FindValues(xAxisField, Convert.ToString(xAxis[x])
, yAxisField, Convert.ToString(yAxis[y]), zAxisFields);
for (int z = 0; z < zAxis; z++) //loop thru
z-axis fields
{
//sub columns
matrix[(((x + 1) *
zAxis - zAxis) + z), y] = zAxisValues[z];
}
}
}
//calculate
totals for the y-axis
decimal[] yTotals = new decimal[(xAxis.Count * zAxis)];
for (int col = 0;
col < (xAxis.Count * zAxis); col++)
{
yTotals[col] = 0;
for (int row = 0; row < yAxis.Count; row++)
{
yTotals[col] += Convert.ToDecimal(matrix[col, row]);
}
}
//calculate
totals for the x-axis
decimal[,] xTotals = new decimal[zAxis,
(yAxis.Count + 1)];
for (int y = 0; y
< yAxis.Count; y++) //loop thru the y-axis
{
int zCount = 0;
for (int z = 0; z < (zAxis * xAxis.Count); z++) //loop thru the z-axis
{
xTotals[zCount, y] += Convert.ToDecimal(matrix[z, y]);
if (zCount == (zAxis - 1))
zCount = 0;
else
zCount++;
}
}
for (int xx = 0;
xx < zAxis; xx++) //Grand Total
{
for (int xy = 0; xy < yAxis.Count; xy++)
{
xTotals[xx,
yAxis.Count] += xTotals[xx, xy];
}
}
//Build
HTML Table
//Append
main row (x-axis)
HtmlTableRow mainRow = new HtmlTableRow();
mainRow.Cells.Add(new HtmlTableCell());
for (int x = 0; x
<= xAxis.Count; x++) //loop thru x-axis + 1
{
HtmlTableCell cell = new HtmlTableCell();
cell.ColSpan = zAxis;
if (x < xAxis.Count)
cell.InnerText = Convert.ToString(xAxis[x]);
else
cell.InnerText = "Grand Totals";
//style cell
MainHeaderTopCellStyle(cell);
mainRow.Cells.Add(cell);
}
table.Rows.Add(mainRow);
//Append sub
row (z-axis)
HtmlTableRow subRow = new HtmlTableRow();
subRow.Cells.Add(new HtmlTableCell());
subRow.Cells[0].InnerText =
yAxisField;
//style
cell
SubHeaderCellStyle(subRow.Cells[0]);
for (int x = 0; x
<= xAxis.Count; x++) //loop thru x-axis + 1
{
for (int z = 0; z < zAxis; z++)
{
HtmlTableCell cell = new HtmlTableCell();
cell.InnerText =
zAxisFields[z];
//style cell
SubHeaderCellStyle(cell);
subRow.Cells.Add(cell);
}
}
table.Rows.Add(subRow);
//Append
table items from matrix
for (int y = 0; y
< yAxis.Count; y++) //loop thru y-axis
{
HtmlTableRow itemRow = new HtmlTableRow();
for (int z = 0; z <= (zAxis * xAxis.Count); z++) //loop thru z-axis + 1
{
HtmlTableCell cell = new HtmlTableCell();
if (z == 0)
{
cell.InnerText = Convert.ToString(yAxis[y]);
//style cell
MainHeaderLeftCellStyle(cell);
}
else
{
cell.InnerText = Convert.ToString(matrix[(z - 1), y]);
//style cell
ItemCellStyle(cell);
}
itemRow.Cells.Add(cell);
}
//append x-axis grand totals
for (int z = 0; z < zAxis; z++)
{
HtmlTableCell cell = new HtmlTableCell();
cell.InnerText = Convert.ToString(xTotals[z, y]);
//style cell
TotalCellStyle(cell);
itemRow.Cells.Add(cell);
}
table.Rows.Add(itemRow);
}
//append y-axis
totals
HtmlTableRow totalRow = new HtmlTableRow();
for (int x = 0; x
<= (zAxis * xAxis.Count); x++)
{
HtmlTableCell cell = new HtmlTableCell();
if (x == 0)
cell.InnerText = "Totals";
else
cell.InnerText = Convert.ToString(yTotals[x - 1]);
//style cell
TotalCellStyle(cell);
totalRow.Cells.Add(cell);
}
//append
x-axis/y-axis totals
for (int z = 0; z
< zAxis; z++)
{
HtmlTableCell cell = new HtmlTableCell();
cell.InnerText = Convert.ToString(xTotals[z,
xTotals.GetUpperBound(1)]);
//style cell
TotalCellStyle(cell);
totalRow.Cells.Add(cell);
}
table.Rows.Add(totalRow);
}
catch
{
throw;
}
return table;
}
/// <summary>
/// Creates a simple 3D Pivot Table.
/// </summary>
/// <param name="xAxisField">The heading at the top of the table.</param>
/// <param name="yAxisField">The heading to the left of the table.</param>
/// <param name="zAxisField">The item value field.</param>
/// <returns></returns>
public HtmlTable PivotTable(string
xAxisField, string yAxisField,
string zAxisField)
{
HtmlTable table = new HtmlTable();
//style table
TableStyle(table);
/*
* The x-axis is the main
horizontal row.
* The z-axis is the sub horizontal
row.
* The y-axis is the left vertical
column.
*/
try
{
//get
distinct xAxisFields
ArrayList xAxis = new ArrayList();
foreach (DataRow row in
_DataTable.Rows)
{
if (!xAxis.Contains(row[xAxisField]))
xAxis.Add(row[xAxisField]);
}
//get
distinct yAxisFields
ArrayList yAxis = new ArrayList();
foreach (DataRow row in
_DataTable.Rows)
{
if (!yAxis.Contains(row[yAxisField]))
yAxis.Add(row[yAxisField]);
}
//create
a 2D array for the x-axis/y-axis fields
string[,] matrix = new string[xAxis.Count, yAxis.Count];
string zAxisValue = "";
for (int y = 0; y
< yAxis.Count; y++) //loop thru y-axis fields
{
//rows
for (int x = 0; x < xAxis.Count; x++) //loop
thru x-axis fields
{
//main columns
//get the z-axis values
zAxisValue =
FindValue(xAxisField, Convert.ToString(xAxis[x])
, yAxisField, Convert.ToString(yAxis[y]), zAxisField);
matrix[x, y] =
zAxisValue;
}
}
//calculate
totals for the y-axis
decimal[] yTotals = new decimal[xAxis.Count];
for (int col = 0;
col < xAxis.Count; col++)
{
yTotals[col] = 0;
for (int row = 0; row < yAxis.Count; row++)
{
yTotals[col] += Convert.ToDecimal(matrix[col, row]);
}
}
//calculate
totals for the x-axis
decimal[] xTotals = new decimal[(yAxis.Count + 1)];
for (int row = 0;
row < yAxis.Count; row++)
{
xTotals[row] = 0;
for (int col = 0; col < xAxis.Count; col++)
{
xTotals[row] += Convert.ToDecimal(matrix[col, row]);
}
}
xTotals[xTotals.GetUpperBound(0)] = 0; //Grand
Total
for (int i = 0; i
< xTotals.GetUpperBound(0); i++)
{
xTotals[xTotals.GetUpperBound(0)] += xTotals[i];
}
//Build
HTML Table
HtmlTableRow heading = new HtmlTableRow();
for (int x = 0; x
< (xAxis.Count + 1); x++)
{
HtmlTableCell cell = new HtmlTableCell();
if (x == 0)
cell.InnerText =
yAxisField;
else
cell.InnerText = Convert.ToString(xAxis[(x - 1)]);
//style cell
MainHeaderTopCellStyle(cell);
heading.Cells.Add(cell);
}
//append
grand totals heading
HtmlTableCell grandTotal = new HtmlTableCell();
grandTotal.InnerText = "Grand Totals";
//style
cell
MainHeaderTopCellStyle(grandTotal);
heading.Cells.Add(grandTotal);
table.Rows.Add(heading);
for (int y = 0; y
< yAxis.Count; y++)
{
HtmlTableRow row = new HtmlTableRow();
for (int x = 0; x < (xAxis.Count + 1); x++)
{
HtmlTableCell cell = new HtmlTableCell();
if (x == 0)
{
cell.InnerText = Convert.ToString(yAxis[y]);
//style cell
MainHeaderLeftCellStyle(cell);
}
else
{
cell.InnerText = Convert.ToString(matrix[(x - 1), y]);
//style cell
ItemCellStyle(cell);
}
row.Cells.Add(cell);
}
//append x-axis totals
HtmlTableCell totalCell = new HtmlTableCell();
totalCell.InnerText = Convert.ToString(xTotals[y]);
//style cell
TotalCellStyle(totalCell);
row.Cells.Add(totalCell);
table.Rows.Add(row);
}
//append
y-axis totals
HtmlTableRow totalRow = new HtmlTableRow();
for (int x = 0; x
<= (xAxis.Count + 1); x++)
{
HtmlTableCell cell = new HtmlTableCell();
if (x == 0)
cell.InnerText = "Totals";
else
if (x <= xAxis.Count)
cell.InnerText = Convert.ToString(yTotals[(x - 1)]);
else
cell.InnerText = Convert.ToString(xTotals[xTotals.GetUpperBound(0)]);
//style cell
TotalCellStyle(cell);
totalRow.Cells.Add(cell);
}
table.Rows.Add(totalRow);
}
catch
{
throw;
}
return table;
}
#endregion Public Methods
}
