using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace friction
{
	public class Report
	{
		public string FilePath { set; get; }
		public string MaterialSpring { set; get; }
		public string MaterialTable { set; get; }
		public DataTable AnalysisData { set; get; }
		public DataTable CompatibilityData { set; get; }
		public List<string> CompatibilityColumns { set; get; }
		public List<string> CompatibilityRows { set; get; }
		public Bitmap RadarChart { set; get; }
		public Bitmap TrendChartByHumidity { set; get; }
		public Bitmap TrendChartByTemperature { set; get; }
		public Bitmap TrendChartByForce { set; get; }
		public Bitmap TrendChartByVelocity { set; get; }

		public void Export()
		{
			using (ExcelPackage package = new ExcelPackage())
			{
				if (package.Workbook.Worksheets.Count < 1)
					package.Workbook.Worksheets.Add("Report");

				ExcelWorksheet Sheet = package.Workbook.Worksheets[1];

				int iRow = 2;
				int iCol = 2;

				Sheet.Cells[iRow, iCol].Value = "Material Spring: " + MaterialSpring;
				Sheet.Cells[iRow, iCol].Style.Font.Bold = true;

				// analysis table
				iRow += 1;
				foreach(DataColumn column in AnalysisData.Columns)
				{
					Sheet.Cells[iRow, iCol].Value = column.ColumnName;
					iCol++;
				}
				iRow++;
				iCol = 2;

				int iTableRowStart = iRow-1;
				int iTableColStart = iCol;
				double dValue;
				int iNoOfTest = 0;
				int iAvgRPNColumn = 1;
				
				foreach(DataRow row in AnalysisData.Rows)
				{
					int.TryParse(row["No. of Tests"].ToString(), out iNoOfTest);

					iCol = 2;
					for (int i = 0; i < AnalysisData.Columns.Count; i++)
					{
						if(double.TryParse(row[i].ToString(), out dValue) == true)
						{
							Sheet.Cells[iRow, iCol].Value = dValue;

							if(AnalysisData.Columns[i].ColumnName == "No. of Tests")
							{

							}
							else if (AnalysisData.Columns[i].ColumnName == "Average RPN")
							{
								Sheet.Cells[iRow, iCol].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;

								var Risk = Config.ANALYSIS.GetRisk((float)dValue);
								switch (Risk)
								{
									case Config.ANALYSIS.RISK.NO:
										Sheet.Cells[iRow, iCol].Style.Fill.BackgroundColor.SetColor(Theme.Green);
										break;

									case Config.ANALYSIS.RISK.POTENTIAL:
										Sheet.Cells[iRow, iCol].Style.Fill.BackgroundColor.SetColor(Theme.Yellow);
										break;

									case Config.ANALYSIS.RISK.HIGH:
										Sheet.Cells[iRow, iCol].Style.Fill.BackgroundColor.SetColor(Theme.Red);
										break;
								}

								iAvgRPNColumn = iCol;
							}
							else
							{
								Sheet.Cells[iRow, iCol].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
								var Dependancy = Config.ANALYSIS.GetDependancy((float)dValue, iNoOfTest);
								switch (Dependancy)
								{
									case Config.ANALYSIS.DEPENDANCY.NO:
										Sheet.Cells[iRow, iCol].Style.Fill.BackgroundColor.SetColor(Theme.Green);
										break;

									case Config.ANALYSIS.DEPENDANCY.POTENTIAL:
										Sheet.Cells[iRow, iCol].Style.Fill.BackgroundColor.SetColor(Theme.Yellow);
										break;

									case Config.ANALYSIS.DEPENDANCY.HIGH:
										Sheet.Cells[iRow, iCol].Style.Fill.BackgroundColor.SetColor(Theme.Red);
										break;

									case Config.ANALYSIS.DEPENDANCY.NOT_ENNOUGH_DATA:
										Sheet.Cells[iRow, iCol].Style.Fill.BackgroundColor.SetColor(Theme.Gray);
										Sheet.Cells[iRow, iAvgRPNColumn].Style.Fill.BackgroundColor.SetColor(Theme.Gray);
										break;
								}
							}
						}
						else
						{
							Sheet.Cells[iRow, iCol].Value = row[i].ToString();
						}
						
						iCol++;
					}

					iRow++;
				}

				Sheet.Cells[iTableRowStart, iTableColStart, iTableRowStart, iCol-1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
				Sheet.Cells[iTableRowStart, iTableColStart, iRow-1, iTableColStart].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
				Sheet.Cells[iTableRowStart, iTableColStart, iRow-1, iCol-1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thick);
				Sheet.Cells[iTableRowStart, iTableColStart, iRow - 1, iCol - 1].AutoFitColumns();

				
				// compatibility table
				iRow += 1;
				iCol = 3;
				foreach (string column in CompatibilityColumns)
				{
					Sheet.Cells[iRow, iCol].Value = column;
					iCol++;
				}
				iRow++;
				iCol = 2;

				iTableRowStart = iRow - 1;
				iTableColStart = iCol;
				dValue = 0;
				iNoOfTest = 0;
				iAvgRPNColumn = 1;

				for(int j=0; j<CompatibilityData.Rows.Count; j++)
				{
					DataRow row = CompatibilityData.Rows[j];
					iCol = 2;
					Sheet.Cells[iRow, iCol].Value = CompatibilityRows[j];
					iCol++;
					for (int i = 0; i < CompatibilityData.Columns.Count; i++)
					{
						if (double.TryParse(row[i].ToString(), out dValue) == true)
						{
							Sheet.Cells[iRow, iCol].Value = dValue;

							Sheet.Cells[iRow, iCol].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;

							var Risk = Config.ANALYSIS.GetRisk((float)dValue);
							switch (Risk)
							{
								case Config.ANALYSIS.RISK.NO:
									Sheet.Cells[iRow, iCol].Style.Fill.BackgroundColor.SetColor(Theme.Green);
									break;

								case Config.ANALYSIS.RISK.POTENTIAL:
									Sheet.Cells[iRow, iCol].Style.Fill.BackgroundColor.SetColor(Theme.Yellow);
									break;

								case Config.ANALYSIS.RISK.HIGH:
									Sheet.Cells[iRow, iCol].Style.Fill.BackgroundColor.SetColor(Theme.Red);
									break;
							}
						}
						else
						{
							Sheet.Cells[iRow, iCol].Value = row[i].ToString();
						}

						iCol++;
					}

					iRow++;
				}

				Sheet.Cells[iTableRowStart, iTableColStart, iTableRowStart, iCol - 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
				Sheet.Cells[iTableRowStart, iTableColStart, iRow - 1, iTableColStart].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
				Sheet.Cells[iTableRowStart, iTableColStart, iRow - 1, iCol - 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thick);
				//Sheet.Cells[iTableRowStart, iTableColStart, iRow - 1, iCol - 1].AutoFitColumns();

				iRow += 2;
				iCol = 2;

				
				// radar graph
				double dX=0;
				double dY=0;
				dX = ExcelHelper.Column2Pixel(Sheet, iCol);
				dY = ExcelHelper.Row2Pixel(Sheet, iRow);

				int iWidth = RadarChart.Width;
				int iHeight = RadarChart.Height;
				iHeight = iHeight * 800 / iWidth;
				iWidth = 800;


				var picRadarChart = Sheet.Drawings.AddPicture("Radar Chart", RadarChart);
				picRadarChart.SetPosition((int)dY, (int)dX);
				picRadarChart.SetSize(iWidth, iHeight);
				dY += iHeight;
				iRow = ExcelHelper.Pixel2NextRow(Sheet, iRow, iHeight);


				if (TrendChartByHumidity != null)
				{
					const int iPictureWidth = 400;

					iRow += 3;
					iCol = 2;

					Sheet.Cells[iRow, iCol].Value = MaterialSpring + " vs " + MaterialTable;
					Sheet.Cells[iRow, iCol].Style.Font.Bold = true;
					iRow += 1;
					dY = ExcelHelper.Row2Pixel(Sheet, iRow);

					var picTrendChartHumidity = Sheet.Drawings.AddPicture("Trend Chart by Humidity", TrendChartByHumidity);
					picTrendChartHumidity.SetPosition((int)dY, (int)dX);
					iWidth = TrendChartByHumidity.Width;
					iHeight = TrendChartByHumidity.Height;
					iHeight = iHeight * iPictureWidth / iWidth;
					iWidth = iPictureWidth;
					picTrendChartHumidity.SetSize(iWidth, iHeight);
					int iPrevRow = iRow;
					iRow = ExcelHelper.Pixel2NextRow(Sheet, iRow, iHeight);
					Sheet.Cells[iRow, iCol].Value = "by Humidity";


					iRow = iPrevRow;
					iCol = ExcelHelper.Pixel2NextColumn(Sheet, iCol, iWidth);
					dX = ExcelHelper.Column2Pixel(Sheet, iCol);
					var picTrendChartTemperature = Sheet.Drawings.AddPicture("Trend Chart by Temperature", TrendChartByTemperature);
					picTrendChartTemperature.SetPosition((int)dY, (int)dX);
					iWidth = TrendChartByTemperature.Width;
					iHeight = TrendChartByTemperature.Height;
					iHeight = iHeight * iPictureWidth / iWidth;
					iWidth = iPictureWidth;
					picTrendChartTemperature.SetSize(iWidth, iHeight);
					iRow = ExcelHelper.Pixel2NextRow(Sheet, iRow, iHeight);
					Sheet.Cells[iRow, iCol].Value = "by Temperature";

					iRow += 2;
					iCol = 2;
					dX = ExcelHelper.Column2Pixel(Sheet, iCol);
					dY = ExcelHelper.Row2Pixel(Sheet, iRow);
					var picTrendChartForce = Sheet.Drawings.AddPicture("Trend Chart by Force", TrendChartByForce);
					picTrendChartForce.SetPosition((int)dY, (int)dX);
					iWidth = TrendChartByForce.Width;
					iHeight = TrendChartByForce.Height;
					iHeight = iHeight * iPictureWidth / iWidth;
					iWidth = iPictureWidth;
					picTrendChartForce.SetSize(iWidth, iHeight);
					iPrevRow = iRow;
					iRow = ExcelHelper.Pixel2NextRow(Sheet, iRow, iHeight);
					Sheet.Cells[iRow, 2].Value = "by Force";

					iRow = iPrevRow;
					iCol = ExcelHelper.Pixel2NextColumn(Sheet, iCol, iWidth);
					dX = ExcelHelper.Column2Pixel(Sheet, iCol);
					var picTrendChartVelocity = Sheet.Drawings.AddPicture("Trend Chart by Velocity", TrendChartByVelocity);
					picTrendChartVelocity.SetPosition((int)dY, (int)dX);
					iWidth = TrendChartByVelocity.Width;
					iHeight = TrendChartByVelocity.Height;
					iHeight = iHeight * iPictureWidth / iWidth;
					iWidth = iPictureWidth;
					picTrendChartVelocity.SetSize(iWidth, iHeight);
					iRow = ExcelHelper.Pixel2NextRow(Sheet, iRow, iHeight);
					Sheet.Cells[iRow, iCol].Value = "by Velocity";
				}

				package.SaveAs(new FileInfo(FilePath));
				Console.WriteLine("Report Saved");
			}
		}
	}
}