/// <summary>
/// build a xref of the columns in the log file
/// </summary>
/// <param name="dataWorksheet">The data worksheet.</param>
/// <returns>Dictionary<System.String, System.Int32>.</returns>
private static Dictionary <string, int> BuildColumnNameXref(SpreadsheetGear.IWorksheet dataWorksheet)
{
Dictionary <string, int> colNameXref = new Dictionary <string, int>();
IRange usedRange = dataWorksheet.UsedRange;
IRange usedColumns = usedRange.Columns;
int columnCount = usedColumns.ColumnCount;
string columnName = string.Empty;
for (int colIndex = 0; colIndex <= columnCount - 1; colIndex++)
{
try
{
columnName = dataWorksheet.Cells[0, colIndex].Text;
if (!string.IsNullOrEmpty(columnName))
{
colNameXref.Add(dataWorksheet.Cells[0, colIndex].Text, colIndex);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
return(colNameXref);
}
public PreparedImage(Workbook workbook, string worksheetName)
{
this.workbook = workbook;
spreadworksheet = InitializationSpreedsheetGearWorksheet(worksheetName);
cells = spreadworksheet.Cells;
ApplyDeaultTitle(worksheetName);
}
private void CreaHoja(string nombreHoja)
{
worksheet = workbook.Worksheets.Add();
cells = worksheet.Cells;
worksheet.Name = nombreHoja;
rowIndex = 1;
counterPage++;
}
/// <summary>
/// We need a objective way to compare the performance between two runs using different tuning constants
/// This approach calculates the sum of the "error area" between the target and the actual
/// The area is calculated as the difference * step time
/// We keep track of the positive (target > actual) and negative (target < actual) error separately
/// Generally these values are displayed in the graph title.
/// </summary>
/// <param name="dataWorksheet"></param>
/// <param name="elapsedDeltaColumnIdx"></param>
/// <param name="targetColumnIdx"></param>
/// <param name="actualColumnIdx"></param>
/// <param name="graphName"></param>
/// <returns></returns>
private static (decimal posErr, decimal negErr) CalcAreaDelta(SpreadsheetGear.IWorksheet dataWorksheet, int elapsedDeltaColumnIdx, int targetColumnIdx, int actualColumnIdx, string graphName)
{
decimal totalPositiveAreaDelta = 0;
decimal totalNegativeAreaDelta = 0;
decimal thisLoopAreaDelta = -0;
int maxRows = dataWorksheet.UsedRange.RowCount;
decimal lastLoopElapsedTimeInMS = 0;
decimal thisLoopElapsedTimeInMS = 0;
decimal targetValue = 0;
decimal actualValue = 0;
int newColumnIdx = dataWorksheet.UsedRange.ColumnCount;
dataWorksheet.Cells[0, newColumnIdx].Value = $"{graphName} Error Area";
for (int rowIndex = 1; rowIndex < maxRows; rowIndex++)
{
thisLoopElapsedTimeInMS = decimal.Parse(dataWorksheet.Cells[rowIndex, elapsedDeltaColumnIdx].Text);
targetValue = decimal.Parse(dataWorksheet.Cells[rowIndex, targetColumnIdx].Text);
actualValue = decimal.Parse(dataWorksheet.Cells[rowIndex, actualColumnIdx].Text);
if (targetValue == 0)
{
continue;
}
thisLoopAreaDelta = Math.Round(((targetValue - actualValue) * thisLoopElapsedTimeInMS), 2);
if (targetValue > actualValue)
{
totalPositiveAreaDelta += thisLoopAreaDelta;
}
else
{
totalNegativeAreaDelta += thisLoopAreaDelta;
}
dataWorksheet.Cells[rowIndex, newColumnIdx].Value = $"{totalPositiveAreaDelta} | {totalNegativeAreaDelta}";
// snapshot for next loop
lastLoopElapsedTimeInMS = thisLoopElapsedTimeInMS;
}
// round result
return(totalPositiveAreaDelta, totalNegativeAreaDelta);
}
private void CorteHoja(string nombreHoja)
{
rowIndex++;
if (rowIndex >= configExcel.CorteHoja)
{
worksheet = workbook.Worksheets.Add();
cells = worksheet.Cells;
worksheet.Name = nombreHoja + headerColumns[counterPage];
rowIndex = 1;
counterPage++;
}
else
{
rowIndex++;
}
}
public void PrintWareHouses(List <WareHousesDTO> wareHouseList, StoreNamesDTO storeName)
{
storeNameDTO = storeName;
SpreadsheetGear.IWorkbook workbook = Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[0];
SpreadsheetGear.IRange cells = worksheet.Cells;
Dictionary <string, byte> HeaderColumn = new Dictionary <string, byte>();
cellList = wareHouseList;
int line = storeNameDTO.LineCount ?? 0;
int column = storeNameDTO.ColumnCount ?? 0;
int cell = storeNameDTO.CellCount ?? 0;
int k = 0;
int startPosition = 1;
int currentPosition = 3;
byte startHeaderPosition = 1;
# region Header
/// <summary>
/// In some scenarios (Telop PID Testing) we may enable in %VBUS mode and some time later transition to VELOCITY mode.
/// The PID constants column may not be populated or valid until we gp into Velocity mode, so...
/// scan down the MODE column until it is Velocity then format & grab the PID values from that row.
/// </summary>
/// <param name="dataWorksheet"></param>
/// <param name="pidGainsColumnIdx"></param>
/// <param name="controlModeColumnIdx"></param>
/// <returns></returns>
private static string GetPIDGains(SpreadsheetGear.IWorksheet dataWorksheet, int pidGainsColumnIdx, int controlModeColumnIdx)
{
int maxRows = dataWorksheet.UsedRange.RowCount;
string controlMode = string.Empty;
// scan down the control mode column looking for the 1st row that is "Velocity", grab the PID gains value from that row
for (int rowIndex = 1; rowIndex < maxRows; rowIndex++)
{
controlMode = dataWorksheet.Cells[rowIndex, controlModeColumnIdx].Text;
switch (controlMode.ToLower())
{
case "velocity":
return(dataWorksheet.Cells[rowIndex, pidGainsColumnIdx].Text);
default:
return(@"N/A Open Loop");
}
}
return(string.Empty);;
}
/// <summary>
/// Perform Fill Percent Summary calculation
/// </summary>
/// <param name="subgroupText"></param>
public void FillPercentSummaryLog(string subgroupText)
{
DataView dvMain = null;
// dvMain.RowFilter = string.Empty;
try
{
//Apply Language Setting
ApplyLanguageSettings();
//Raise event for wait cursor
this.RaiseEventSetCursor(false);
int i;
int j;
int k = 0;
string sFilter = string.Empty;
int CtrFillFrom = 0;
int CtrFillTo = 0;
int iSelCount = Step1SelectionDataTable.Rows.Count;
int iRecordCount = 0;
string TempStringForAddress = string.Empty;
String[,] msArray = new String[1, 9]; // Log sheet array
// Indicator and Subgroup val NIds will be used for SSel_NId
//string[] sSel_NId;
string Sel_Indicator_NId = string.Empty;
string Sel_Subgroup_NId = string.Empty;
ArrayList sSel_Indicator_NId = new ArrayList();
ArrayList sSel_Subgroup_Val_NId = new ArrayList();
//Variable for fill data value and formula
string sChageValue = string.Empty;
string sAddress = string.Empty;
string sNum = string.Empty;
string sDen = string.Empty;
string sMinRange = string.Empty;
string sMaxRange = string.Empty;
SpreadsheetGear.IRange oRngStart;
SpreadsheetGear.IRange oRngTotal;
int iDataCol = 0;
bool SupressArea = false;
//Dim NumTotal, DenTotal As Single
double NumTotal;
double DenTotal;
int FirstNumIndex;
// Raise Event for initialize Progress Bar
RaiseEventSetProgress(1);
// Copy log file to temp location
if (File.Exists(LogFilePath))
{
File.Copy(LogFilePath, TempLogFilePath, true);
System.IO.File.SetAttributes(TempLogFilePath, FileAttributes.Normal);
}
// Getting workbook using BAL DIExcel
this.DIExcel = new DIExcel(TempLogFilePath, System.Globalization.CultureInfo.CurrentCulture);
// Getting worksheets 1.Summery sheet 2. LogSheet
SummarySheet = DIExcel.GetWorksheet(0);
LogSheet = DIExcel.GetWorksheet(1);
// Setting Lang based Name of worksheets
SummarySheet.Name = LangStrings.SUMMARY;
LogSheet.Name = LangStrings.LOG;
RaiseEventSetProgress(10);
// Filling General info of summery sheet
SummarySheet.Cells[0, 0].Value = LangStrings.VAR_WIZARD_NAME;
SummarySheet.Cells[2, 0].Value = LangStrings.sMODULE;
SummarySheet.Cells[2, 0].Value = LangStrings.VAR_WIZARD_NAME;
SummarySheet.Cells[3, 0].Value = LangStrings.DATE_TIME;
SummarySheet.Cells[3, 1].Value = TimeStamp;
SummarySheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME;
SummarySheet.Cells[4, 1].Value = "Log_Percent (" + TimeStamp + ").xls";
SummarySheet.Cells[6, 0].Value = LangStrings.DECIMALPLACES;
SummarySheet.Cells[6, 1].Value = this.DecimalUpDownControlValue;
SummarySheet.Cells[7, 2].Value = LangStrings.NUMERATOR;
SummarySheet.Cells[7, 2 + Step1SelectionDataTable.Rows.Count + 1].Value = LangStrings.DENOMINATOR;
SummarySheet.Cells[7, 2 + Step1SelectionDataTable.Rows.Count + 1 + Step2SelectionDataTable.Rows.Count + 1].Value = LangStrings.TARGET;
SummarySheet.Cells[10, 0].Value = LangStrings.AREAID;
SummarySheet.Cells[10, 1].Value = LangStrings.AREANAME;
// Raise Event to notify progress
RaiseEventSetProgress(25);
//*** Get Distinct Selected IndicatorNId & Subgroup_Val_NId From Step1 and Step2 and fill in summary sheet
//Loop for all Indicator row selected in step1
for (i = 0; i <= Step1SelectionDataTable.Rows.Count - 1; i++)
{
// Set selected indicator and subgroup val name in excel c10 and c11 and so on
//If second indicator and sg are selected at step 1 its values goes in d10 and d11
SummarySheet.Cells[9, 2 + i].Value = Step1SelectionDataTable.Rows[i][Indicator.IndicatorName].ToString(); // indicator text
SummarySheet.Cells[10, 2 + i].Value = Step1SelectionDataTable.Rows[i][SubgroupVals.SubgroupVal].ToString(); // subgroupVal text
sSel_Indicator_NId.Add(Step1SelectionDataTable.Rows[i][Indicator.IndicatorNId].ToString());
sSel_Subgroup_Val_NId.Add(Step1SelectionDataTable.Rows[i][SubgroupVals.SubgroupValNId].ToString());
}
// Set total caption
SummarySheet.Cells[10, 2 + i].Value = LangStrings.TOTAL;
//--------------------------------------------------------------------
//Working with step2 selection (Selected Denominator).Entering them in excel sheet
for (i = 0; i <= Step2SelectionDataTable.Rows.Count - 1; i++)
{
SummarySheet.Cells[9, 2 + Step1SelectionDataTable.Rows.Count + 1 + i].Value = Step2SelectionDataTable.Rows[i][Indicator.IndicatorName].ToString(); // indicator text
SummarySheet.Cells[10, 2 + Step1SelectionDataTable.Rows.Count + 1 + i].Value = Step2SelectionDataTable.Rows[i][SubgroupVals.SubgroupVal].ToString();
// update distinct indicator NId
if (sSel_Indicator_NId.Contains(Step2SelectionDataTable.Rows[i][Indicator.IndicatorNId].ToString()) == false)
{
sSel_Indicator_NId.Add(Step2SelectionDataTable.Rows[i][Indicator.IndicatorNId].ToString());
}
// update distinct indicator NId and subgroup NIDs
if (sSel_Subgroup_Val_NId.Contains(Step2SelectionDataTable.Rows[i][SubgroupVals.SubgroupValNId].ToString()) == false)
{
sSel_Subgroup_Val_NId.Add(Step2SelectionDataTable.Rows[i][SubgroupVals.SubgroupValNId].ToString());
}
}
SummarySheet.Cells[10, 2 + Step1SelectionDataTable.Rows.Count + 1 + i].Value = LangStrings.TOTAL;
i += 1;
SummarySheet.Cells[9, 2 + Step1SelectionDataTable.Rows.Count + 1 + i].Value = DataEntrySheetInfo.Indicator;
SummarySheet.Cells[10, 2 + Step1SelectionDataTable.Rows.Count + 1 + i].Value = DESheetInformation.Unit;// DESUnitText;
//*** Get the data view with all filters applied
dvMain = this.PresentationData;
//*** Filter records for selected Indicator / Subgroup in calculates step1
if (dvMain.RowFilter == "")
{
dvMain.RowFilter = Indicator.IndicatorNId + " IN (" + string.Join(",", (string[])sSel_Indicator_NId.ToArray(typeof(string))) + ") AND " + SubgroupVals.SubgroupValNId + " IN (" + string.Join(",", (string[])sSel_Subgroup_Val_NId.ToArray(typeof(string))) + ")";
}
else
{
dvMain.RowFilter += " AND " + Indicator.IndicatorNId + " IN (" + String.Join(",", (string[])sSel_Indicator_NId.ToArray(typeof(string))) + ") AND " + SubgroupVals.SubgroupValNId + " IN (" + string.Join(",", (string[])sSel_Subgroup_Val_NId.ToArray(typeof(string))) + ")";
}
sFilter = dvMain.RowFilter;
//Fill Unique Area
dvMain.Sort = Area.AreaID + " ASC, " + Timeperiods.TimePeriod + " DESC";
CtrFillFrom = 12;//13;
for (i = 0; i <= dvMain.Count - 1; i++)
{
if (dvMain[i][Area.AreaID].ToString() != SummarySheet.Cells[iRecordCount + CtrFillFrom - 1, 0].Value + "")
{
SummarySheet.Cells[iRecordCount + CtrFillFrom, 0].Value = dvMain[i][Area.AreaID].ToString();
SummarySheet.Cells[iRecordCount + CtrFillFrom, 1].Value = dvMain[i][Area.AreaName].ToString();
iRecordCount += 1;
}
}
// Getting Last Fill Column.
CtrFillTo = CtrFillFrom + iRecordCount - 1;
//Raise Event to notify Progress
RaiseEventSetProgress(35);
//*** Set Data Array for Log Sheet simultaneously
msDataArray = new String[iRecordCount, 6];
msArray = new String[(iRecordCount * (Step1SelectionDataTable.Rows.Count + Step2SelectionDataTable.Rows.Count + 2)) + 2, 9];
msArray[0, 0] = LangStrings.AREAID;
msArray[0, 1] = LangStrings.AREANAME;
msArray[0, 2] = LangStrings.ELEMENT;
msArray[0, 3] = LangStrings.INDICATOR;
msArray[0, 4] = LangStrings.SUBGROUP;
msArray[0, 5] = LangStrings.TIME;
msArray[0, 6] = LangStrings.DATAVALUE;
msArray[0, 7] = LangStrings.UNIT;
msArray[0, 8] = LangStrings.SOURCE;
k += 2;
//Fill Data Values and Formula for each records
for (i = CtrFillFrom; i <= CtrFillTo; i++)
{
//*** Fill Datavalue for Numerator Indicator against each AreaID
NumTotal = 0;
DenTotal = 0;
FirstNumIndex = 0;
for (j = 0; j <= Step1SelectionDataTable.Rows.Count - 1; j++)
{
iDataCol = 3 + j;
dvMain.RowFilter = sFilter + " AND " + Area.AreaID + " = '" + Utility.DICommon.EscapeWildcardChar(Utility.DICommon.RemoveQuotes(SummarySheet.Cells[i, 0].Value.ToString())) + "'";
// Getting Indicator and subgroup Val NId
Sel_Indicator_NId = Step1SelectionDataTable.Rows[j][Indicator.IndicatorNId].ToString();
Sel_Subgroup_NId = Step1SelectionDataTable.Rows[j][SubgroupVals.SubgroupValNId].ToString();
dvMain.RowFilter += " AND " + Indicator.IndicatorNId + " =" + Sel_Indicator_NId + " AND " + SubgroupVals.SubgroupValNId + " =" + Sel_Subgroup_NId;
if (dvMain.Count > 0)
{
if (FirstNumIndex == 0)
{
FirstNumIndex = k;
}
try
{
SummarySheet.Cells[i, iDataCol - 1].Value = dvMain[0][Data.DataValue].ToString();
NumTotal += Convert.ToDouble(dvMain[0][Data.DataValue].ToString());
}
catch (Exception ex)
{
}
if (SupressArea == false)
{
msArray[k, 0] = dvMain[0][Area.AreaID].ToString();
msArray[k, 1] = dvMain[0][Area.AreaName].ToString();
msDataArray[i - 12, 0] = dvMain[0][Timeperiods.TimePeriod].ToString();
msDataArray[i - 12, 1] = dvMain[0][Area.AreaID].ToString();
msDataArray[i - 12, 2] = dvMain[0][Area.AreaName].ToString();
msDataArray[i - 12, 3] = dvMain[0][Data.DataValue].ToString();
//msDataArray[i - 13, 4] = cbo1_3_Subgroup.Text;
msDataArray[i - 12, 4] = subgroupText;
msDataArray[i - 12, 5] = dvMain[0][IndicatorClassifications.ICName].ToString();
SupressArea = true;
}
msArray[k, 2] = LangStrings.NUMERATOR;
msArray[k, 3] = dvMain[0][Indicator.IndicatorName].ToString();
msArray[k, 4] = dvMain[0][SubgroupVals.SubgroupVal].ToString();
msArray[k, 5] = dvMain[0][Timeperiods.TimePeriod].ToString();
msArray[k, 6] = dvMain[0][Data.DataValue].ToString();
msArray[k, 7] = dvMain[0][Unit.UnitName].ToString();
msArray[k, 8] = dvMain[0][IndicatorClassifications.ICName].ToString();
k += 1;
}
}
//*** Fill Datavalue for Denominator Indicator against each AreaID
for (j = 0; j <= Step2SelectionDataTable.Rows.Count - 1; j++)
{
iDataCol = 3 + Step1SelectionDataTable.Rows.Count + 1 + j;
dvMain.RowFilter = sFilter + " AND " + Area.AreaID + " = '" + Utility.DICommon.EscapeWildcardChar(Utility.DICommon.RemoveQuotes(SummarySheet.Cells[i, 0].Value.ToString())) + "'";
//sSel_NId = lv1_2_Selected.Items(j).Tag.ToString.Split("_");
Sel_Indicator_NId = Step2SelectionDataTable.Rows[j][Indicator.IndicatorNId].ToString();
Sel_Subgroup_NId = Step2SelectionDataTable.Rows[j][SubgroupVals.SubgroupValNId].ToString();
dvMain.RowFilter += " AND " + Indicator.IndicatorNId + " =" + Sel_Indicator_NId + " AND " + SubgroupVals.SubgroupValNId + " =" + Sel_Subgroup_NId;
if (dvMain.Count > 0)
{
SummarySheet.Cells[i, iDataCol - 1].Value = dvMain[0][Data.DataValue].ToString();
try
{
DenTotal += Convert.ToDouble(dvMain[0][Data.DataValue].ToString());
}
catch (Exception ex)
{
}
if (SupressArea == false)
{
msArray[k, 0] = dvMain[0][Area.AreaID].ToString();
msArray[k, 1] = dvMain[0][Area.AreaName].ToString();
msDataArray[i - 12, 0] = dvMain[0][Timeperiods.TimePeriod].ToString();
msDataArray[i - 12, 1] = dvMain[0][Area.AreaID].ToString();
msDataArray[i - 12, 2] = dvMain[0][Area.AreaName].ToString();
//msDataArray[i - 13, 4] = cbo1_3_Subgroup.Text;
msDataArray[i - 12, 3] = subgroupText;
msDataArray[i - 12, 4] = dvMain[0][IndicatorClassifications.ICName].ToString();
SupressArea = true;
}
msArray[k, 2] = LangStrings.DENOMINATOR;
//*** Bugfix Feb 06 Hardcoded Numerator instead of Denominator
msArray[k, 3] = dvMain[0][Indicator.IndicatorName].ToString();
msArray[k, 4] = dvMain[0][SubgroupVals.SubgroupVal].ToString();
msArray[k, 5] = dvMain[0][Timeperiods.TimePeriod].ToString();
msArray[k, 6] = dvMain[0][Data.DataValue].ToString();
msArray[k, 7] = dvMain[0][Unit.UnitName].ToString();
msArray[k, 8] = dvMain[0][IndicatorClassifications.ICName].ToString();
k += 1;
}
}
if (FirstNumIndex > 0)
{
msArray[k, 2] = LangStrings.PERCENT;
msArray[k, 3] = msArray[FirstNumIndex, 3];
msArray[k, 4] = msArray[FirstNumIndex, 4];
msArray[k, 5] = msArray[FirstNumIndex, 5];
if (DenTotal != 0)
msArray[k, 6] = Convert.ToString(Math.Round((100 * NumTotal / DenTotal), (int)DecimalUpDownControlValue));
msArray[k, 7] = LangStrings.PERCENT;
msArray[k, 8] = msArray[FirstNumIndex, 8];
}
k += 2;
SupressArea = false;
}
//*** Set the formula for Numerator Total
sChageValue = "";
for (j = 0; j <= Step1SelectionDataTable.Rows.Count - 1; j++)
{
iDataCol = 3 + j;
if (Strings.Trim(sChageValue) == "")
{
sChageValue = SummarySheet.Cells[CtrFillFrom, iDataCol - 1].Address.Replace("$", "");
}
else
{
TempStringForAddress = SummarySheet.Cells[CtrFillFrom, iDataCol - 1].Address.Replace("$", "");
sChageValue = sChageValue + " + " + TempStringForAddress;
}
}
sChageValue = "IF(" + sChageValue + "=\"\",\"\"," + sChageValue + ")";
sAddress = SummarySheet.Cells[CtrFillFrom, iDataCol].Address;
SummarySheet.Range[sAddress].Formula = "= " + sChageValue;
TempStringForAddress = SummarySheet.Cells[CtrFillFrom, iDataCol].Address.Replace("$", "");
oRngStart = SummarySheet.Range[TempStringForAddress];
string TempStringForAddress2 = SummarySheet.Cells[CtrFillTo, iDataCol].Address.Replace("$", "");
if (TempStringForAddress != TempStringForAddress2)
{
sMaxRange = TempStringForAddress + ":" + TempStringForAddress2;
oRngTotal = SummarySheet.Range[sMaxRange];
AutoFillRange(oRngStart, oRngTotal);
}
//*** Set the formula for Denominator Total
sChageValue = "";
for (j = 0; j <= Step2SelectionDataTable.Rows.Count - 1; j++)
{
iDataCol = 3 + Step1SelectionDataTable.Rows.Count + 1 + j;
if (Strings.Trim(sChageValue) == "")
{
sChageValue = SummarySheet.Cells[CtrFillFrom, iDataCol - 1].Address.Replace("$", "");
}
else
{
TempStringForAddress = SummarySheet.Cells[CtrFillFrom, iDataCol - 1].Address.Replace("$", "");
sChageValue = sChageValue + " + " + TempStringForAddress;
}
}
sChageValue = "IF(" + sChageValue + "=\"\",\"\"," + sChageValue + ")";
sAddress = SummarySheet.Cells[CtrFillFrom, iDataCol].Address;
SummarySheet.Range[sAddress].Formula = "= " + sChageValue;
TempStringForAddress = SummarySheet.Cells[CtrFillFrom, iDataCol].Address.Replace("$", "");
TempStringForAddress2 = SummarySheet.Cells[CtrFillTo, iDataCol].Address.Replace("$", "");
oRngStart = SummarySheet.Range[TempStringForAddress];
if (TempStringForAddress != TempStringForAddress2)
{
sMaxRange = TempStringForAddress + ":" + TempStringForAddress2;
oRngTotal = SummarySheet.Range[sMaxRange];
//oRngStart.AutoFill(Destination = oRngTotal);
AutoFillRange(oRngStart, oRngTotal);
}
//*********************** Set the formula for Percent **********************************
sChageValue = "";
iDataCol = 3 + Step1SelectionDataTable.Rows.Count + 1 + Step2SelectionDataTable.Rows.Count + 1;
sNum = SummarySheet.Cells[CtrFillFrom, 2 + Step1SelectionDataTable.Rows.Count].Address.Replace("$", "");
sDen = SummarySheet.Cells[CtrFillFrom, 2 + Step1SelectionDataTable.Rows.Count + 1 + Step2SelectionDataTable.Rows.Count].Address.Replace("$", "");
sChageValue = "IF(OR(" + sNum + "=\"\" ," + sDen + "=\"\"),\"\",ROUND((" + sNum + " * 100 / " + sDen + ")," + SummarySheet.Cells[6, 1].Address + "))";
sAddress = SummarySheet.Cells[CtrFillFrom, iDataCol - 1].Address;
// Set Formula
SummarySheet.Range[sAddress].Formula = "= " + sChageValue;
//Apply formula to range
TempStringForAddress = SummarySheet.Cells[CtrFillFrom, iDataCol - 1].Address.Replace("$", "");
TempStringForAddress2 = SummarySheet.Cells[CtrFillTo, iDataCol - 1].Address.Replace("$", "");
oRngStart = SummarySheet.Range[TempStringForAddress];
if (TempStringForAddress != TempStringForAddress2)
{
sMaxRange = TempStringForAddress + ":" + TempStringForAddress2;
oRngTotal = SummarySheet.Range[sMaxRange];
this.AutoFillRange(oRngStart, oRngTotal);
}
//*** Setting the Font
try
{
SummarySheet.Range[0, 0, CtrFillTo, iDataCol - 1].Font.Name = FontName;
SummarySheet.Range[0, 0, CtrFillTo, iDataCol - 1].Font.Size = (float)FontSize;
SummarySheet.Range[7, 0, CtrFillTo, iDataCol - 1].Columns.AutoFit();
SummarySheet.Range[CtrFillFrom, 2, CtrFillTo, iDataCol - 1].HorizontalAlignment = HAlign.Right; //xlRight
SummarySheet.Cells[0, 0].Font.Bold = true;
SummarySheet.Cells[0, 0].Font.Italic = true;
SummarySheet.Cells[0, 0].Font.Size = TitleFontSize;
SummarySheet.Range[7, 0, 7, iDataCol - 1].Font.Bold = true;
}
catch (Exception ex)
{
}
//*********************************** S H O W L O G **************************************
LogSheet.Cells[0, 0].Value = LangStrings.VAR_LOG_NAME;
LogSheet.Cells[2, 0].Value = LangStrings.sMODULE;
LogSheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME;
LogSheet.Cells[3, 0].Value = LangStrings.DATE_TIME;
LogSheet.Cells[3, 1].Value = TimeStamp;
LogSheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME;
LogSheet.Cells[4, 1].Value = "Log_Percent (" + TimeStamp + ").xls";
CtrFillFrom = 8;// 9 in Excel;
//*** Set Data
LogSheet.Range[CtrFillFrom, 0, CtrFillFrom + msArray.GetLength(0) - 1, 8].Value = msArray;
//*** Autofit
LogSheet.Range[CtrFillFrom - 2, 0, CtrFillFrom + msArray.GetLength(0) - 1, 8].Columns.AutoFit();
LogSheet.Range[CtrFillFrom, 5, CtrFillFrom + msArray.GetLength(0) - 1, 5].HorizontalAlignment = HAlign.Left; //-4131;
//xlLeft 'Time column
//*** Set Font
LogSheet.Range[0, 0, CtrFillFrom + msArray.GetLength(0) - 1, 8].Font.Name = FontName;
LogSheet.Range[0, 0, CtrFillFrom + msArray.GetLength(0) - 1, 8].Font.Size = (float)FontSize;
LogSheet.Cells[0, 0].Font.Bold = true;
LogSheet.Cells[0, 0].Font.Italic = true;
LogSheet.Cells[0, 0].Font.Size = TitleFontSize;
//******************************************************************************************
// Activate forst sheet and save workbook
DIExcel.GetWorksheet(0).Cells[0, 0].Activate();
DIExcel.Save();
this.RaiseEventSetProgress(100);
//*** hide progress bar
this.RaiseEventHideProgressBar();
// Dispose worksheets
SummarySheet = null;
LogSheet = null;
dvMain.RowFilter = string.Empty;
}
catch (Exception ex)
{
}
finally
{
this.RaiseEventSetCursor(true);
}
// Raise event to show workbook Show workbook inside panel
this.RaiseEventCalculateStepCompleted("pnl1_4");
}
/// <summary>
/// Perform Composite Index Calculations
/// </summary>
/// <param name="compositeIndexStep2DataTable"></param>
/// <param name="scaleMinDecimalValue">nud3_3_Min.Value</param>
/// <param name="scaleMaxDecimalValue">nud3_3_Max.Value;</param>
/// <param name="showSubgroup"></param>
/// <param name="step3SubgroupText"></param>
public void FillCompositeIndexSummaryLog(DataTable compositeIndexStep2DataTable, decimal scaleMinDecimalValue, decimal scaleMaxDecimalValue, bool showSubgroup, String step3SubgroupText)
{
// Info required Step1DataTable,scaleMinDecimalValue,scaleMaxDecimalValue
// ScaleMinValue,
//CompositeIndexStep2DataTable having columns Indicator ,Percent weight, HighIsGood
// showSubgroup
//cbo3_3_Subgroup.Text as step3SubgroupText
int i = 0;
int j = 0;
int k = 0;
string sFilter = string.Empty;
int iCtrFillFrom = 0;
int iCtrFillTo = 0;
int iSelCount = Step1SelectionDataTable.Rows.Count;
int iRecordCount = 0;
string[] sSel_NId = new string[2];
string sSel_Indicator_NId = "";
string sSel_Subgroup_Val_NId = "";
string TempFromAddressString = string.Empty;
string TempToAddressString = string.Empty;
string[,] msArray = new string[1, 8];
try
{
//Set cursor to wait cursor
this.RaiseEventSetCursor(false);
// Apply Language Settings
ApplyLanguageSettings();
//*** Set Progress bar
this.RaiseEventSetProgress(1);
// Get temp log file
if (File.Exists(this.LogFilePath))
{
File.Copy(this.LogFilePath, this.TempLogFilePath, true);
System.IO.File.SetAttributes(TempLogFilePath, FileAttributes.Normal);
}
//open file and get worksheets
this.DIExcel = new DIExcel(TempLogFilePath);
SummarySheet = DIExcel.GetWorksheet(0);
LogSheet = DIExcel.GetWorksheet(1);
SummarySheet.Name = LangStrings.SUMMARY;
LogSheet.Name = LangStrings.LOG;
//*************************** SHOW INDEX ******************************************
// Raise Event to notify progess
this.RaiseEventSetProgress(10);
//*** enter lang based info in summery sheet
SummarySheet.Cells[0, 0].Value = LangStrings.VAR_WIZARD_NAME;
SummarySheet.Cells[2, 0].Value = LangStrings.sMODULE;
SummarySheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME;
SummarySheet.Cells[3, 0].Value = LangStrings.DATE_TIME;
SummarySheet.Cells[3, 1].Value = TimeStamp;
SummarySheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME;
SummarySheet.Cells[4, 1].Value = "Log_Composite Index (" + TimeStamp + ").xls";
SummarySheet.Cells[6, 3].Value = LangStrings.DECIMALPLACES;
SummarySheet.Cells[6, 4].Value = this.DecimalUpDownControlValue;
SummarySheet.Cells[6, 0].Value = LangStrings.SCALE_MIN;
SummarySheet.Cells[6, 1].Value = scaleMinDecimalValue;
SummarySheet.Cells[7, 0].Value = LangStrings.SCALE_MAX;
SummarySheet.Cells[7, 1].Value = scaleMaxDecimalValue;
SummarySheet.Cells[9, 0].Value = LangStrings.PERCENT_WEIGHT;
SummarySheet.Cells[10, 0].Value = LangStrings.HIGHISGOOD;
for (i = 2; i <= iSelCount * 2; i += 2)
{
SummarySheet.Cells[9, i - 1].Value = compositeIndexStep2DataTable.Rows[i / 2 - 1][1].ToString();// dg3_2_Weight[i / 2 - 1, 1).ToString();
SummarySheet.Cells[10, i - 1].Value = compositeIndexStep2DataTable.Rows[i / 2 - 1][2].ToString();
SummarySheet.Cells[10, i - 1].HorizontalAlignment = HAlign.Right; //xlRight
SummarySheet.Cells[13, i - 1].Value = compositeIndexStep2DataTable.Rows[i / 2 - 1][0].ToString();
SummarySheet.Cells[13, i].Value = LangStrings.INDEX;
}
SummarySheet.Cells[13, 0].Value = Area.AreaName;
SummarySheet.Cells[13, i - 1].Value = "Score";
SummarySheet.Cells[13, i].Value = "Composite Index";
SummarySheet.Cells[13, i + 1].Value = Area.AreaID;
this.RaiseEventSetProgress(25);
//*** Get Selected Indicator/I_S NId
for (i = 0; i <= iSelCount - 1; i++)
{
sSel_NId[0] = Step1SelectionDataTable.Rows[i][Indicator.IndicatorNId].ToString();
sSel_NId[1] = Step1SelectionDataTable.Rows[i][SubgroupVals.SubgroupValNId].ToString();
if (i == 0)
{
if (showSubgroup)
{
sSel_Indicator_NId = sSel_NId[0];
sSel_Subgroup_Val_NId = sSel_NId[1];
}
else
{
sSel_Indicator_NId = sSel_NId[0];
}
}
else
{
if (showSubgroup)
{
sSel_Indicator_NId = sSel_Indicator_NId + "," + sSel_NId[0];
sSel_Subgroup_Val_NId = sSel_Subgroup_Val_NId + "," + sSel_NId[1];
}
else
{
sSel_Indicator_NId = sSel_Indicator_NId + "," + sSel_NId[0];
}
}
}
//*** Get the data view with all filters applied
DataView dvMain = this.PresentationData;
//*** Filter records for selected Indicator / Subgroup in calculates step1
if (showSubgroup)
{
sFilter = Indicator.IndicatorNId + " IN (" + sSel_Indicator_NId + ") AND " + SubgroupVals.SubgroupValNId + " IN (" + sSel_Subgroup_Val_NId + ")";
}
else
{
sFilter = Indicator.IndicatorNId + " IN (" + sSel_Indicator_NId + ")";
}
if (dvMain.RowFilter == "")
{
dvMain.RowFilter = sFilter;
}
else
{
dvMain.RowFilter += " AND " + sFilter;
}
sFilter = dvMain.RowFilter;
//Fill Unique Area
dvMain.Sort = " Area_ID ASC";
iCtrFillFrom = 16;
for (i = 0; i <= dvMain.Count - 1; i++)
{
if (dvMain[i][Area.AreaID].ToString() != SummarySheet.Cells[iRecordCount + iCtrFillFrom - 1, iSelCount * 2 + 3].Value + "")
{
SummarySheet.Cells[iRecordCount + iCtrFillFrom, 0].Value = dvMain[i][Area.AreaName];
SummarySheet.Cells[iRecordCount + iCtrFillFrom, iSelCount * 2 + 3].Value = dvMain[i][Area.AreaID];
iRecordCount += 1;
}
}
iCtrFillTo = iCtrFillFrom + iRecordCount - 1;
//Update progress
this.RaiseEventSetProgress(35);
//*** Set Data Array for Log Sheet simultaneously
msDataArray = new string[iRecordCount, 6];
msArray = new string[(iRecordCount * iSelCount) + iRecordCount + 3, 8];
msArray[0, 0] = LangStrings.AREAID;
msArray[0, 1] = LangStrings.AREANAME;
msArray[0, 2] = LangStrings.INDICATOR;
msArray[0, 3] = LangStrings.SUBGROUP;
msArray[0, 4] = LangStrings.TIME;
msArray[0, 5] = LangStrings.DATAVALUE;
msArray[0, 6] = LangStrings.UNIT;
msArray[0, 7] = LangStrings.SOURCE;
k += 2;
//Fill Data Values and Formula for each records
string sStaticValue = string.Empty;
string sScoreStaticValue = "";
string sHiddenDivision = "";
string sChageValue = string.Empty;
string sAddess = string.Empty;
string sMinRange = string.Empty;
string sMaxRange = string.Empty;
IRange oRngStart;
IRange oRngTotal;
int iDataCol = 0;
bool bSupressArea = false;
//*** varibales for setting Avg Data Value for missing Data
int[] arrDataCount = new int[iSelCount];
//float[] arrDataSum = new float[iSelCount];
double[] arrDataSum = new double[iSelCount];
string[] arrCellId = new string[iSelCount];
for (i = iCtrFillFrom; i <= iCtrFillTo; i++)
{
//*** Fill Datavalue for all selected indecator against each AreaID
for (j = 0; j <= iSelCount - 1; j++)
{
iDataCol = (j + 1) * 2;
dvMain.RowFilter = sFilter + " AND " + Area.AreaID + " = '" + Utility.DICommon.EscapeWildcardChar(Utility.DICommon.RemoveQuotes(SummarySheet.Cells[i, iSelCount * 2 + 3].Value.ToString())) + "'";
//sSel_NId = lv3_1_Selected.Items(j).Tag.ToString.Split("_");
sSel_NId[0] = Step1SelectionDataTable.Rows[j][Indicator.IndicatorNId].ToString();
sSel_NId[1] = Step1SelectionDataTable.Rows[j][SubgroupVals.SubgroupValNId].ToString();
if (showSubgroup)
{
dvMain.RowFilter += " AND " + Indicator.IndicatorNId + " =" + sSel_NId[0] + " AND " + SubgroupVals.SubgroupValNId + " =" + sSel_NId[1];
}
else
{
dvMain.RowFilter += " AND " + Indicator.IndicatorNId + " =" + sSel_NId[0];
}
if (dvMain.Count == 0)
{
SummarySheet.Cells[i, iDataCol - 1].Value = 0;
SummarySheet.Cells[i, iDataCol - 1].NumberFormat = "0.00";
SummarySheet.Cells[i, iDataCol - 1].Font.Bold = true;
SummarySheet.Cells[i, iDataCol - 1].Font.ColorIndex = 15;
arrCellId[j] += i + "," +Convert.ToInt32(iDataCol-1) + ";";
}
else
{
SummarySheet.Cells[i, iDataCol - 1].Value = dvMain[0][Data.DataValue].ToString();
if (bSupressArea == false)
{
msArray[k, 0] = dvMain[0][Area.AreaID].ToString();
msArray[k, 1] = dvMain[0][Area.AreaName].ToString();
msDataArray[i - iCtrFillFrom, 0] = dvMain[0][Timeperiods.TimePeriod].ToString();
msDataArray[i - iCtrFillFrom, 1] = dvMain[0][Area.AreaID].ToString();
msDataArray[i - iCtrFillFrom, 2] = dvMain[0][Area.AreaName].ToString();
msDataArray[i - iCtrFillFrom, 4] = step3SubgroupText;
//dvMain[0]["Subgroup_Val")
msDataArray[i - iCtrFillFrom, 5] = dvMain[0][IndicatorClassifications.ICName].ToString();
bSupressArea = true;
}
msArray[k, 2] = dvMain[0][Indicator.IndicatorName].ToString();
msArray[k, 3] = dvMain[0][SubgroupVals.SubgroupVal].ToString();
msArray[k, 4] = dvMain[0][Timeperiods.TimePeriod].ToString();
msArray[k, 5] = dvMain[0][Data.DataValue].ToString();
msArray[k, 6] = dvMain[0][Unit.UnitName].ToString();
msArray[k, 7] = dvMain[0][IndicatorClassifications.ICName].ToString();
arrDataCount[j] += 1;
arrDataSum[j] += Convert.ToDouble(dvMain[0][Data.DataValue]);
k += 1;
}
if (i == iCtrFillFrom)
{
//*** Build and Set Formula For Index Columns
sStaticValue = SummarySheet.Cells[10, iDataCol - 1].Address;
//*** Extract the Alphabet part from cell address. Accomodate condition that cell address may be like A1 or AA1
TempFromAddressString = SummarySheet.Cells[0, iDataCol - 1].Address.Replace("$", "");
sChageValue = TempFromAddressString;// Strings.Replace(SummarySheet.Cells[1, iDataCol].Address, "$", "");
sChageValue = (Strings.Len(sChageValue) == 3 ? Strings.Mid(sChageValue, 1, 2) : Strings.Mid(sChageValue, 1, 1));
sChageValue = sChageValue + Convert.ToString(iCtrFillFrom + 1);
sMinRange = "Min(" + SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address + ":" + SummarySheet.Cells[iCtrFillTo, iDataCol - 1].Address + ")";
sMaxRange = "Max(" + SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address + ":" + SummarySheet.Cells[iCtrFillTo, iDataCol - 1].Address + ")";
sAddess = SummarySheet.Cells[iCtrFillFrom, iDataCol].Address;
SummarySheet.Range[sAddess].Formula = "= IF((" + sMaxRange + " - " + sMinRange + ") = 0" + "," + 1 + "," + "IF(" + sStaticValue + "," + "(" + sChageValue + " - " + sMinRange + ")" + " / " + "(" + sMaxRange + " - " + sMinRange + ")," + " " + "(" + sChageValue + " - " + sMaxRange + ")" + " / " + "(" + sMinRange + " - " + sMaxRange + ")))";
TempFromAddressString = SummarySheet.Cells[iCtrFillFrom, iDataCol].Address.Replace("$", "");
oRngStart = SummarySheet.Range[TempFromAddressString];
TempToAddressString = SummarySheet.Cells[iCtrFillTo, iDataCol].Address.Replace("$", "");
if (TempFromAddressString != TempToAddressString)
{
sMaxRange = TempFromAddressString + ":" + TempToAddressString;
oRngTotal = SummarySheet.Range[sMaxRange];
this.AutoFillRange(oRngStart, oRngTotal);
}
//*** Build Formula for Score Column
if (Strings.Trim(sScoreStaticValue) == "")
{
//TempFromAddressString = SummarySheet.Cells[iCtrFillFrom, iDataCol + 1].Address.Replace("$", "");
sScoreStaticValue = TempFromAddressString + "*" + SummarySheet.Cells[9, iDataCol - 1].Address;
sHiddenDivision = SummarySheet.Cells[9, iDataCol - 1].Address;
}
else
{
sScoreStaticValue = sScoreStaticValue + " + " + TempFromAddressString + "*" + SummarySheet.Cells[9, iDataCol - 1].Address;
sHiddenDivision = sHiddenDivision + " + " + SummarySheet.Cells[9, iDataCol - 1].Address;
}
}
}
//*** Set Formaula for all Index Colunns against each AreaID
if (i == iCtrFillFrom)
{
//*** Set Formula for Score Column
sAddess = SummarySheet.Cells[iCtrFillFrom, iDataCol + 1].Address;
SummarySheet.Range[sAddess].Formula = "= (" + sScoreStaticValue + ") / " + "(" + sHiddenDivision + ")";
TempToAddressString = SummarySheet.Cells[iCtrFillTo, iDataCol + 1].Address.Replace("$", "");
TempFromAddressString = SummarySheet.Cells[iCtrFillFrom, iDataCol + 1].Address.Replace("$", "");
oRngStart = SummarySheet.Range[TempFromAddressString];
if (TempFromAddressString != TempToAddressString)
{
sMaxRange = TempFromAddressString + ":" + TempToAddressString;
oRngTotal = SummarySheet.Range[sMaxRange];
//oRngStart.AutoFill(Destination = oRngTotal);
this.AutoFillRange(oRngStart, oRngTotal);
}
//*** Set Formula for Composite Index
string sHiddenColumn;
sHiddenColumn = (Strings.Len(Strings.Mid(sMaxRange, 1, Strings.InStr(1, sMaxRange, ":", CompareMethod.Text) - 1)) == 4 ? Strings.Mid(sMaxRange, 1, 2) : Strings.Mid(sMaxRange, 1, 1));
sMinRange = "Min(" + SummarySheet.Cells[iCtrFillFrom, iDataCol + 1].Address + ":" + SummarySheet.Cells[iCtrFillTo, iDataCol + 1].Address + ")";
sMaxRange = "Max(" + SummarySheet.Cells[iCtrFillFrom, iDataCol + 1].Address + ":" + SummarySheet.Cells[iCtrFillTo, iDataCol + 1].Address + ")";
sStaticValue = "IF((" + sMaxRange + " - " + sMinRange + ") = 0" + ", " + "$B$8" + "," + "$B$7" + " + " + "( (" + sHiddenColumn + 17 + " - " + sMinRange + ") / (" + sMaxRange + " - " + sMinRange + ") ) * (" + "$B$8" + " - " + "$B$7" + "))";
//*** Set formula for Decimal Precision based on Cells(7, 5) value.
//*** Rounding should be cell address based so that if user makes changes inside excel it should reflect
sStaticValue = "ROUND(" + sStaticValue + "," + SummarySheet.Cells[6, 4].Address + ")";
sAddess = SummarySheet.Cells[iCtrFillFrom, iDataCol + 2].Address;
SummarySheet.Range[sAddess].Formula = "= " + sStaticValue;
SummarySheet.Cells[iCtrFillFrom, iDataCol + 2].NumberFormat = "General";
TempFromAddressString = SummarySheet.Cells[iCtrFillFrom, iDataCol + 2].Address.Replace("$", "");
TempToAddressString = SummarySheet.Cells[iCtrFillTo, iDataCol + 2].Address.Replace("$", "");
oRngStart = SummarySheet.Range[TempFromAddressString];
if (TempFromAddressString != TempToAddressString)
{
sMaxRange = TempFromAddressString + ":" + TempToAddressString;
oRngTotal = SummarySheet.Range[sMaxRange];
this.AutoFillRange(oRngStart, oRngTotal);
}
}
k += 1;
bSupressArea = false;
}
//*** Set Avg data value for Missing Data
string[] sCellId;
string[] sRowCol;
float MissingData=0.0f;
for (i = 0; i <= iSelCount - 1; i++)
{
if ((arrCellId[i] != null))
{
// Get Missing Data : DataSum/ DataCount
try
{
MissingData = (float)arrDataSum[i] / arrDataCount[i];
}
catch (Exception)
{
}
sCellId = arrCellId[i].ToString().Split(';');
for (j = 0; j <= sCellId.Length - 1; j++)
{
if (sCellId[j].ToString().Trim() != "")
{
sRowCol = sCellId[j].Split(',');
SummarySheet.Cells[Convert.ToInt32(sRowCol[0]), Convert.ToInt32(sRowCol[1])].Value = MissingData;
}
}
}
}
//*** Setting the Font
SummarySheet.Range[0, 0, iCtrFillTo, iDataCol + 3].Font.Name = this.FontName;// goUserPreference.Language.FontName;
SummarySheet.Range[0, 0, iCtrFillTo, iDataCol + 3].Font.Size = (float)this.FontSize; //goUserPreference.Language.FontSize;
SummarySheet.Range[6, 0, iCtrFillTo, iDataCol + 3].Columns.AutoFit();
SummarySheet.Range[13, 1, 14, iSelCount * 2 + 3].HorizontalAlignment = HAlign.Right;// -4152;
//xlRight
SummarySheet.Cells[0, 0].Font.Bold = true;
SummarySheet.Cells[0, 0].Font.Italic = true;
SummarySheet.Cells[0, 0].Font.Size = this.TitleFontSize;
//*** Progress
//moStatusBar.progressBar.Value = 45;
this.RaiseEventSetProgress(45);
//*********************************** S H O W L O G **************************************
LogSheet.Cells[0, 0].Value = LangStrings.VAR_LOG_NAME;
LogSheet.Cells[2, 0].Value = LangStrings.sMODULE;
LogSheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME;
LogSheet.Cells[3, 0].Value = LangStrings.DATE_TIME;
LogSheet.Cells[3, 1].Value = TimeStamp;
LogSheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME;
LogSheet.Cells[4, 1].Value = "Log_Composite Index (" + TimeStamp + ").xls";
iCtrFillFrom = 8; //9;
//*** Set Data
LogSheet.Range[iCtrFillFrom, 0, iCtrFillFrom + msArray.GetLength(0) - 1, 7].Value = msArray;
//*** Autofit
LogSheet.Range[iCtrFillFrom - 2, 0, iCtrFillFrom + msArray.GetLength(0) - 1, 7].Columns.AutoFit();
//*** Set Font
LogSheet.Range[0, 0, iCtrFillFrom + msArray.GetLength(0) - 2, 7].Font.Name = this.FontName; //goUserPreference.Language.FontName;
LogSheet.Range[1, 1, iCtrFillFrom + msArray.GetLength(0) - 2, 8].Font.Size = (float)this.FontSize; //goUserPreference.Language.FontSize;
LogSheet.Cells[0, 0].Font.Bold = true;
LogSheet.Cells[0, 0].Font.Italic = true;
LogSheet.Cells[0, 0].Font.Size = this.TitleFontSize;
//*** hide progress bar
this.RaiseEventSetProgress(100);
DIExcel.Save();
this.RaiseEventHideProgressBar();
this.RaiseEventStep3_4Completed();
SummarySheet = null;
LogSheet = null;
dvMain.RowFilter = string.Empty;
}
catch (Exception ex)
{
}
finally
{
this.RaiseEventSetCursor(true);
}
}
/// <summary>
/// Perform 100Minus Summary calculation
/// </summary>
public void Fill100MinusSummaryLog()
{
try
{
// Apply Language settings
ApplyLanguageSettings();
RaiseEventSetCursor(false);
//Excel.Worksheet
int i = 0;
int iAreaCount = 0;
int iCtrFillFrom;
int iCtrFillTo;
int iDataCol;
string TempStringForEndCellAddress = string.Empty;
string sAddress = string.Empty;
DataView dvMain;
System.Collections.Specialized.StringCollection oSubgroup = new System.Collections.Specialized.StringCollection();
//*** Initialize Progress Bar
this.RaiseEventSetProgress(1);
// Get calculate file to temp location
if (File.Exists(this.LogFilePath))
{
File.Copy(this.LogFilePath, this.TempLogFilePath, true);
System.IO.File.SetAttributes(TempLogFilePath, FileAttributes.Normal);
}
// Opening and getting workbook
this.DIExcel = new DIExcel(this.TempLogFilePath);
// Getting worksheet
SummarySheet = DIExcel.GetWorksheet(0);
LogSheet = DIExcel.GetWorksheet(1);
SummarySheet.Name = LangStrings.SUMMARY;
LogSheet.Name = LangStrings.LOG;
//*************************** SHOW INDEX ******************************************
RaiseEventSetProgress(10);
//*** Get the data view with all filters applied
dvMain = this.PresentationData;
//dvMain.RowFilter = string.Empty;
//if (string.IsNullOrEmpty(dvMain.RowFilter))
//{
dvMain.RowFilter = Indicator.IndicatorNId + " = " + Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId].ToString();
//}
//else
//{
// dvMain.RowFilter += Indicator.IndicatorNId + " = " + Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId].ToString();
//}
dvMain.Sort = Area.AreaID + " ASC";
//DataRowView drv;
//msDataArray = new string[dvMain.Count - 1, 5];
msDataArray = new string[dvMain.Count, 6];
// string[,] msSummaryArray = new string[dvMain.Count-1 + 3, 8];
int summaryArrayLengh = 1;
if (dvMain.Count>0)
{
summaryArrayLengh = dvMain.Count + 3;
}
string[,] msSummaryArray = new string[summaryArrayLengh, 8];
msSummaryArray[0, 0] = LangStrings.AREAID;
msSummaryArray[0, 1] = LangStrings.AREANAME;
msSummaryArray[0, 2] = Step1SelectionDataTable.Rows[0][0].ToString();// lv2_1_Selected.Items(0).SubItems(0).Text;
msSummaryArray[0, 3] = LangStrings.VAR_WIZARD_NAME;
msSummaryArray[0, 4] = LangStrings.SUBGROUP;
msSummaryArray[0, 5] = LangStrings.TIME;
msSummaryArray[0, 6] = LangStrings.UNIT;
msSummaryArray[0, 7] = LangStrings.SOURCE;
string PrevAreaId = "";
i += 2;
foreach (DataRowView drv in dvMain)
{
//*** Fill Datavalue for Numerator Indicator against each AreaID
if (PrevAreaId != drv[Area.AreaID].ToString())
{
iAreaCount += 1;
PrevAreaId = drv[Area.AreaID].ToString();
msSummaryArray[i, 0] = drv[Area.AreaID].ToString();
msSummaryArray[i, 1] = drv[Area.AreaName].ToString();
}
msSummaryArray[i, 2] = drv[Data.DataValue].ToString();
msSummaryArray[i, 3] = System.Convert.ToString(100 - System.Convert.ToDouble(drv["Data_Value"].ToString()));
msSummaryArray[i, 4] = drv[SubgroupVals.SubgroupVal].ToString();
if (oSubgroup.Contains(drv[SubgroupVals.SubgroupValNId].ToString()) == false)
{
oSubgroup.Add(drv[SubgroupVals.SubgroupValNId].ToString());
}
msSummaryArray[i, 5] = drv[Timeperiods.TimePeriod].ToString();
msSummaryArray[i, 6] = drv[Unit.UnitName].ToString();
msSummaryArray[i, 7] = drv[IndicatorClassifications.ICName].ToString();
msDataArray[i - 2, 0] = drv[Timeperiods.TimePeriod].ToString();
msDataArray[i - 2, 1] = drv[Area.AreaID].ToString();
msDataArray[i - 2, 2] = drv[Area.AreaName].ToString();
msDataArray[i - 2, 4] = drv[SubgroupVals.SubgroupVal].ToString();
msDataArray[i - 2, 5] = drv[IndicatorClassifications.ICName].ToString();
i += 1;
}
string[,] msLogArray = new string[dvMain.Count - 1 + iAreaCount + 3, 9];
//*** Data Array for Log Sheet
msLogArray[0, 0] = LangStrings.AREAID;
msLogArray[0, 1] = LangStrings.AREANAME;
msLogArray[0, 2] = LangStrings.INDICATOR;
msLogArray[0, 3] = LangStrings.SUBGROUP;
msLogArray[0, 4] = LangStrings.TIME;
msLogArray[0, 5] = LangStrings.DATAVALUE;
msLogArray[0, 6] = LangStrings.UNIT;
msLogArray[0, 7] = LangStrings.SOURCE;
msLogArray[0, 8] = LangStrings.VAR_WIZARD_NAME;
PrevAreaId = "";
i = 1;
dvMain.GetEnumerator().Reset();
foreach (DataRowView dvRow in dvMain)
{
if (PrevAreaId != dvRow[Area.AreaID].ToString())
{
i += 1;
PrevAreaId = dvRow[Area.AreaID].ToString();
msLogArray[i, 0] = dvRow[Area.AreaID].ToString();
msLogArray[i, 1] = dvRow[Area.AreaName].ToString();
}
msLogArray[i, 2] = dvRow[Indicator.IndicatorName].ToString();
msLogArray[i, 3] = dvRow[SubgroupVals.SubgroupVal].ToString();
msLogArray[i, 4] = dvRow[Timeperiods.TimePeriod].ToString();
msLogArray[i, 5] = dvRow[Data.DataValue].ToString();
msLogArray[i, 6] = dvRow[Unit.UnitName].ToString();
msLogArray[i, 7] = dvRow[IndicatorClassifications.ICName].ToString();
msLogArray[i, 8] = Convert.ToString(100 - Convert.ToDouble(dvRow[Data.DataValue]));
i += 1;
}
//*** Special Handling for Subgroup in 100 Minus
this.DESheetInformation.Subgroup = new string[oSubgroup.Count];
this.DESheetInformation.SubgroupGUID = new string[oSubgroup.Count];
for (i = 0; i <= oSubgroup.Count - 1; i++)
{
string sSql = string.Empty;
// sSql = this.DBQueries.Subgroup.GetSubgroupVals(Lib.DI_LibDAL.Queries.FilterFieldType.NId, oSubgroup[i]);
sSql = this.DBQueries.SubgroupVals.GetSubgroupVals(Lib.DI_LibDAL.Queries.FilterFieldType.NId, oSubgroup[i]);
dvMain = this.DBConnection.ExecuteDataTable(sSql).DefaultView;
this.DESheetInformation.Subgroup[i] = dvMain[0][SubgroupVals.SubgroupVal].ToString();
if (dvMain.Count > 0)
{
this.DESheetInformation.SubgroupGUID[i] = dvMain[0][SubgroupVals.SubgroupValGId].ToString();
}
else
{
this.DESheetInformation.SubgroupGUID[i] = Guid.NewGuid().ToString();
}
}
SummarySheet.Cells[0, 0].Value = LangStrings.VAR_WIZARD_NAME;
SummarySheet.Cells[2, 0].Value = LangStrings.sMODULE;
SummarySheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME;
SummarySheet.Cells[3, 0].Value = LangStrings.DATE_TIME;
SummarySheet.Cells[3, 1].Value = TimeStamp;
SummarySheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME;
SummarySheet.Cells[4, 1].Value = "Log_100 Minus (" + TimeStamp + ").xls";
SummarySheet.Cells[6, 0].Value = LangStrings.DECIMALPLACES;
SummarySheet.Cells[6, 1].Value = this.DecimalUpDownControlValue;
this.RaiseEventSetProgress(25);
//************************ Set Data in summary sheet********************
SummarySheet.Range[8, 0, 8 + msSummaryArray.GetLength(0) - 1, 7].Value = msSummaryArray;
this.RaiseEventSetProgress(35);
//Fill Data Values and Formula for each records
string sChangeValue;
string sMinRange;
string sMaxRange;
IRange oRngStart;
IRange oRngTotal;
//*** Set the formula for 100 Minus
iCtrFillFrom = 10;
iCtrFillTo = iCtrFillFrom + msSummaryArray.GetLength(0) - 2 - 2;
if (iCtrFillTo<iCtrFillFrom)
{
iCtrFillTo = iCtrFillFrom;
}
// -2 for header row and a blank row
iDataCol = 4;
sChangeValue = "";
TempStringForEndCellAddress = SummarySheet.Cells[iCtrFillFrom, 2].Address.Replace("$", "");
sChangeValue = "ROUND(100 - " + TempStringForEndCellAddress + "," + SummarySheet.Cells[6, 1].Address + ")";
sAddress = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address;
// Set Formula
SummarySheet.Range[sAddress].Formula = "= " + sChangeValue;
// Apply formula for all rows
sAddress = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address.Replace("$", "");
oRngStart = SummarySheet.Range[sAddress];
TempStringForEndCellAddress = SummarySheet.Cells[iCtrFillTo, iDataCol - 1].Address.Replace("$", "");
if (sAddress != TempStringForEndCellAddress)
{
sAddress = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address.Replace("$", "");
TempStringForEndCellAddress = SummarySheet.Cells[iCtrFillTo, iDataCol - 1].Address.Replace("$", "");
sMaxRange = sAddress + ":" + TempStringForEndCellAddress;
oRngTotal = SummarySheet.Range[sMaxRange];
AutoFillRange(oRngStart, oRngTotal);
}
iDataCol = 8;
//*** Setting the Font
SummarySheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Name = this.FontName;
SummarySheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Size = (float)this.FontSize;
try
{
SummarySheet.Range[8, 0, iCtrFillTo, iDataCol - 1].Columns.AutoFit();
SummarySheet.Range[iCtrFillFrom, 4, iCtrFillTo, 4].HorizontalAlignment = HAlign.Left; // -4131;
}
catch (Exception ex)
{
}
//xlLeft 'Time column
SummarySheet.Cells[0, 0].Font.Bold = true;
SummarySheet.Cells[0, 0].Font.Italic = true;
SummarySheet.Cells[0, 0].Font.Size = this.TitleFontSize;
//Raise event to notify Progress
this.RaiseEventSetProgress(45);
//*********************************** S H O W L O G **************************************
LogSheet.Cells[0, 0].Value = LangStrings.VAR_LOG_NAME;
LogSheet.Cells[2, 0].Value = LangStrings.sMODULE;
LogSheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME;
LogSheet.Cells[3, 0].Value = LangStrings.DATE_TIME;
LogSheet.Cells[3, 1].Value = TimeStamp;
LogSheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME;
LogSheet.Cells[4, 1].Value = "Log_100 Minus (" + TimeStamp + ").xls";
iCtrFillFrom = 8;
//*** Set Data
LogSheet.Range[iCtrFillFrom, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 8].Value = msLogArray;
//NOte1 : Index change completed
//*** Autofit
LogSheet.Range[iCtrFillFrom, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 8].Columns.AutoFit();
LogSheet.Range[iCtrFillFrom, 4, iCtrFillFrom + msLogArray.GetLength(0) - 1, 4].HorizontalAlignment = HAlign.Left;// -4131;
//xlLeft 'Time column
//*** Set Font
LogSheet.Range[0, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 8].Font.Name = this.FontName;
LogSheet.Range[0, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 8].Font.Size = (float)this.FontSize;
LogSheet.Cells[0, 0].Font.Bold = true;
LogSheet.Cells[0, 0].Font.Italic = true;
LogSheet.Cells[0, 0].Font.Size = this.TitleFontSize;
//******************************************************************************************
// Save Workbook
DIExcel.GetWorksheet(0).Cells[0, 0].Activate();
DIExcel.Save();
this.RaiseEventSetProgress(100);
//Raise event for hiding progress bar
this.RaiseEventHideProgressBar();
// Dispose
SummarySheet = null;
LogSheet = null;
dvMain.RowFilter = string.Empty;
}
catch (Exception ex)
{
}
finally
{
RaiseEventSetCursor(true);
}
// RaiseEvent step completed
this.RaiseEventCalculateStepCompleted("pnl2_3");
}
/// <summary>
/// Get Data From Summary sheet for creation of DES
/// </summary>
public void GetDESheetDataFromSummarySheet(System.Globalization.CultureInfo currentCulture)
{
//System.Threading.Thread thisThread = System.Threading.Thread.CurrentThread;
//System.Globalization.CultureInfo originalCulture = thisThread.CurrentCulture;
try
{
// set current thread culture to english
//thisThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
int i = 0;
int j = 0;
int iDataCol = 0;
//this.DIExcel = new DIExcel(TempLogFilePath);
this.DIExcel = new DIExcel(TempLogFilePath, currentCulture);
IWorksheet SummarySheet = DIExcel.GetWorksheet(0);
//Excel.Worksheet
switch (ApplicationWizardType)
{
case WizardType.Percent:
//*** Decimal
if (Information.IsNumeric(SummarySheet.Cells[6, 1].Value))
{
this.DESheetInformation.Decimals = Convert.ToInt32(SummarySheet.Cells[6, 1].Value);
this.DecimalUpDownControlValue = this.DESheetInformation.Decimals;
}
iDataCol = 3 + Step1SelectionDataTable.Rows.Count + Step2SelectionDataTable.Rows.Count;
//*** Indicator & Unit
SetDESheetIUSGuid(SummarySheet.Cells[9, iDataCol + 1].Text, SummarySheet.Cells[10, iDataCol + 1].Text, "");
// raise to set step3 indicator and unit text
RaiseEventSetStep3IndicatorAndUnit(DESheetInformation.Indicator, this.DESheetInformation.Unit);
//*** Data Values
for (i = 0; i <= msDataArray.GetLength(0) - 1; i++)
{
//msDataArray(i, 3) = SummarySheet.Cells[i + 12, iDataCol].value;
msDataArray[i, 3] = SummarySheet.Cells[i + 12, iDataCol + 1].Text;
}
break;
case WizardType.HundredMinus:
//*** Decimal
if (Information.IsNumeric(SummarySheet.Cells[6, 1].Value))
{
this.DESheetInformation.Decimals = Convert.ToInt32(SummarySheet.Cells[6, 1].Value);
this.DecimalUpDownControlValue = (decimal)this.DESheetInformation.Decimals;
}
//*** Data Values
iDataCol = 3;
for (i = 0; i <= msDataArray.GetLength(0) - 1; i++)
{
msDataArray[i, 3] = SummarySheet.Cells[i + 10, iDataCol].Value.ToString();
}
break;
case WizardType.CompositeIndex:
//*** Decimal
if (Information.IsNumeric(SummarySheet.Cells[6, 4].Value))
{
this.DESheetInformation.Decimals = Convert.ToInt32(SummarySheet.Cells[6, 4].Value);
this.DecimalUpDownControlValue = (decimal)this.DESheetInformation.Decimals;
}
//*** Data Values
iDataCol = 2 + (this.Step1SelectionDataTable.Rows.Count * 2) ; //*** 1 for Score Col
//*** Handling Excel Sorting for Composite Index
string[] ArrAreaId = new string[msDataArray.GetLength(0)];
for (i = 0; i <= msDataArray.GetLength(0) - 1; i++)
{
ArrAreaId[i] = msDataArray[i, 1];
}
string[,] sTempDataArray = new string[msDataArray.GetLength(0), msDataArray.GetLength(1)];
int ArrIndex;
for (i = 0; i <= msDataArray.GetLength(0) - 1; i++)
{
ArrIndex = Array.IndexOf(ArrAreaId, SummarySheet.Cells[i + 16, iDataCol+1].Value);
for (j = 0; j <= msDataArray.GetLength(1) - 1; j++)
{
if (j == 3)
{
sTempDataArray[i, 3] = SummarySheet.Cells[i + 16, iDataCol].Value.ToString();
}
else
{
sTempDataArray[i, j] = msDataArray[ArrIndex, j];
}
}
}
msDataArray = null;
msDataArray = sTempDataArray;
break;
case WizardType.SubTotal:
// No decimal Combo
//Data Values
//Subtotal Step 5
iDataCol = 3;
for (i = 0; i <= (msDataArray.GetLength(0) / this.DESheetInformation.SubgroupGUID.Length) - 1; i++)
{
for (j = 0; j <= this.DESheetInformation.SubgroupGUID.Length - 1; j++)
{
msDataArray[i + j, 3] = SummarySheet.Cells[i + 11, (iDataCol + j) - 1].Value.ToString();
}
}
break;
case WizardType.TransformUnit:
////*** Decimal
if (Information.IsNumeric(SummarySheet.Cells[6, 1].Value))
{
this.DESheetInformation.Decimals = Convert.ToInt32(SummarySheet.Cells[6, 1].Value);
this.DecimalUpDownControlValue = (decimal)this.DESheetInformation.Decimals;
}
//*** Data Values
iDataCol = 5;
for (i = 0; i <= msDataArray.GetLength(0) - 1; i++)
{
msDataArray[i, 3] = SummarySheet.Cells[i + 10, iDataCol].Value.ToString();
}
break;
case WizardType.UserDefinedFormula:
//*** Decimal
if (Information.IsNumeric(SummarySheet.Cells[6, 1].Value))
{
this.DESheetInformation.Decimals = Convert.ToInt32(SummarySheet.Cells[6, 1].Value);
this.DecimalUpDownControlValue = (decimal)this.DESheetInformation.Decimals;
}
//*** Data Values
iDataCol = 2 + this.SelectedNids.Count;
for (i = 0; i <= msDataArray.GetLength(0) - 1; i++)
{
msDataArray[i, 3] = SummarySheet.Cells[i + 11, iDataCol].Value.ToString();
}
break;
}
SummarySheet = null;
}
catch (Exception ex)
{
}
// Reset Original culture
//thisThread.CurrentCulture = originalCulture;
}
/// <summary>
/// Perform Calculations for User defind formula
/// </summary>
/// <param name="FullFormulaText">txt6_1FullFormula text in UI application</param>
/// <param name="formulaText">txt6_1Formula text</param>
/// <param name="showSubgroup">mbShowsubgrop</param>
/// <param name="step2SubgroupText">cbo6_2_Subgroup.Text</param>
/// <param name="selNId2">arraylist miSelNId2 'Used for storing NID of Sel. Indicator </param>
/// <param name="sIdentifierChar">msIdentifierChar</param>
public void FillUserDefinedFormulaSummaryLog(string FullFormulaText, string formulaText, bool showSubgroup, string step2SubgroupText, ArrayList selNId2, StringCollection sIdentifierChar)
{
System.Threading.Thread thisThread = System.Threading.Thread.CurrentThread;
System.Globalization.CultureInfo originalCulture = thisThread.CurrentCulture;
try
{
thisThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
this.RaiseEventSetCursor(false);
IWorksheet SummarySheet;
IWorksheet LogSheet;
int i = 0;
int j = 0;
int k = 0;
string sFilter = string.Empty;
int iCtrFillFrom = 0;
int iCtrFillTo = 0;
int iRecordCount = 0;
string[] sSel_NId;
string sSel_Indicator_NId = "";
string sSel_Subgroup_Val_NId = "";
DataView dvMain;
string[,] msArray = new string[1, 8]; // For holding data
//Variables for set formula
string sChageValue = string.Empty;
string sAddress = string.Empty;
string sMinRange = string.Empty;
string sMaxRange = string.Empty;
IRange oRngStart;
IRange oRngTotal;
string TempFillFromString = string.Empty;
string TempFillToString = string.Empty;
this.RaiseEventSetProgress(1);
//*** Initialize Progress Bar
// Copy Log File tempalate to temp location
if (File.Exists(this.LogFilePath))
{
File.Copy(this.LogFilePath, this.TempLogFilePath, true);
System.IO.File.SetAttributes(TempLogFilePath, FileAttributes.Normal);
}
// Apply language settings
ApplyLanguageSettings();
// Opening and getting workbook
this.DIExcel = new DIExcel(TempLogFilePath);
SummarySheet = DIExcel.GetWorksheet(0);
LogSheet = DIExcel.GetWorksheet(1);
// Setting name for worksheets
SummarySheet.Name = LangStrings.SUMMARY;
LogSheet.Name = LangStrings.LOG;
//*************************** SHOW INDEX ******************************************
this.RaiseEventSetProgress(10);
// Getting presentation data in dvMain Dataview
dvMain = this.PresentationData;
//*** Data Array for Log Sheet
// Filling Fixed columns of summery sheet with lang based strings
SummarySheet.Cells[0, 0].Value = LangStrings.VAR_WIZARD_NAME;
SummarySheet.Cells[2, 0].Value = LangStrings.sMODULE;
SummarySheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME;
SummarySheet.Cells[3, 0].Value = LangStrings.DATE_TIME;
SummarySheet.Cells[3, 1].Value = this.TimeStamp;
SummarySheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME;
SummarySheet.Cells[4, 1].Value = "Log_User Defined Formula (" + this.TimeStamp + ").xls";
SummarySheet.Cells[6, 0].Value = LangStrings.DECIMALPLACES;
SummarySheet.Cells[6, 1].Value = this.DecimalUpDownControlValue; //nud6_2_Decimals.Value;
//"Decimal Value"
SummarySheet.Cells[9, 0].Value = LangStrings.AREAID;
SummarySheet.Cells[9, 1].Value = LangStrings.AREANAME;
SummarySheet.Cells[9, 2 + this.SelectedNids.Count].Value = FullFormulaText;
this.RaiseEventSetProgress(25);
//*** Get Selected I_S NId
// SelectedNid is an arraylist passed by clinet application
for (i = 0; i <= this.SelectedNids.Count - 1; i++)
{
// Split selected Nid to get indicator ad subgroup val NId
sSel_NId = this.SelectedNids[i].ToString().Split('_');
if (i == 0)
{
sSel_Indicator_NId = sSel_NId[0];
sSel_Subgroup_Val_NId = sSel_NId[1];
}
// Get comma delimited list of selected NIds
else
{
sSel_Indicator_NId = sSel_Indicator_NId + "," + sSel_NId[0];
sSel_Subgroup_Val_NId = sSel_Subgroup_Val_NId + "," + sSel_NId[1];
}
SummarySheet.Cells[8, 2 + i].Value = this.Step1SelectionDataTable.Rows[Convert.ToInt32(selNId2[i])][0].ToString(); // lv6_1_Available.Items(selNId2[i]).SubItems(0).Text;
SummarySheet.Cells[9, 2 + i].Value = this.Step1SelectionDataTable.Rows[Convert.ToInt32(selNId2[i])][1].ToString();// lv6_1_Available.Items(selNId2[i]).SubItems(1).Text;
}
//*** Filter records for selected Indicator / Subgroup in calculates step1 . using mbshowSubgroup
if (showSubgroup)
{
sFilter = Indicator.IndicatorNId + " IN (" + sSel_Indicator_NId + ") AND " + SubgroupVals.SubgroupValNId + " IN (" + sSel_Subgroup_Val_NId + ")";
}
else
{
sFilter = Indicator.IndicatorNId + " IN (" + sSel_Indicator_NId + ")";
}
//*** Bugfix 21 Dec 2006 Data Filters not applied
if (dvMain.RowFilter == "")
{
}
else
{
sFilter = dvMain.RowFilter + " AND " + sFilter;
}
//Fill Unique Area
dvMain.Sort = Area.AreaID + " ASC";
iCtrFillFrom = 11;// 12;
for (i = 0; i <= dvMain.Count - 1; i++)
{
//if (SummarySheet.Cells[iRecordCount + iCtrFillFrom , 0].Value ==null || dvMain[i]["Area_ID"].ToString() != SummarySheet.Cells[iRecordCount + iCtrFillFrom-1 , 0].Value.ToString() + "")
if (SummarySheet.Cells[iRecordCount + iCtrFillFrom - 1, 0].Value == null || dvMain[i]["Area_ID"].ToString() != SummarySheet.Cells[iRecordCount + iCtrFillFrom - 1, 0].Value.ToString() + "")
{
SummarySheet.Cells[iRecordCount + iCtrFillFrom, 0].Value = dvMain[i][Area.AreaID].ToString();
SummarySheet.Cells[iRecordCount + iCtrFillFrom, 1].Value = dvMain[i][Area.AreaName].ToString();
iRecordCount += 1;
}
}
iCtrFillTo = iCtrFillFrom + iRecordCount - 1;
this.RaiseEventSetProgress(35);
//*** Set Data Array for Log Sheet simultaneously
msDataArray = new string[iRecordCount, 6];
msArray = new string[(iRecordCount * this.SelectedNids.Count) + iRecordCount + 3, 8];
msArray[0, 0] = LangStrings.AREAID;
msArray[0, 1] = LangStrings.AREANAME;
msArray[0, 2] = LangStrings.INDICATOR;
msArray[0, 3] = LangStrings.SUBGROUP;
msArray[0, 4] = LangStrings.TIME;
msArray[0, 5] = LangStrings.DATAVALUE;
msArray[0, 6] = LangStrings.UNIT;
msArray[0, 7] = LangStrings.SOURCE;
k += 2;
//*** Fill Data Values and Formula for each Area
int iDataCol;
bool bSupressArea = false;
for (i = iCtrFillFrom; i <= iCtrFillTo; i++)
{
for (j = 0; j <= this.SelectedNids.Count - 1; j++)
{
iDataCol = 3 + j;
sSel_NId = this.SelectedNids[j].ToString().Split('_');
dvMain.RowFilter = sFilter + " AND " + Area.AreaID + " = '" + Utility.DICommon.EscapeWildcardChar(Utility.DICommon.RemoveQuotes(SummarySheet.Cells[i, 0].Value.ToString())) + "'";
dvMain.RowFilter += " AND " + Indicator.IndicatorNId + "=" + sSel_NId[0] + " AND Subgroup_Val_NId =" + sSel_NId[1];
if (dvMain.Count > 0)
{
SummarySheet.Cells[i, iDataCol - 1].Value = dvMain[0][Data.DataValue].ToString();
//SummarySheet.Cells[i, iDataCol - 1].Value =dvMain[0]["Data_Value"].ToString().Replace(",",".");
if (bSupressArea == false)
{
msArray[k, 0] = dvMain[0][Area.AreaID].ToString();
msArray[k, 1] = dvMain[0][Area.AreaName].ToString();
msDataArray[i - iCtrFillFrom, 0] = dvMain[0][Timeperiods.TimePeriod].ToString();
msDataArray[i - iCtrFillFrom, 1] = dvMain[0][Area.AreaID].ToString();
msDataArray[i - iCtrFillFrom, 2] = dvMain[0][Area.AreaName].ToString();
msDataArray[i - iCtrFillFrom, 4] = step2SubgroupText;
msDataArray[i - iCtrFillFrom, 5] = dvMain[0][IndicatorClassifications.ICName].ToString();
bSupressArea = true;
}
msArray[k, 2] = dvMain[0][Indicator.IndicatorName].ToString();
msArray[k, 3] = dvMain[0][SubgroupVals.SubgroupVal].ToString();
msArray[k, 4] = dvMain[0][Timeperiods.TimePeriod].ToString();
msArray[k, 5] = dvMain[0][Data.DataValue].ToString();
msArray[k, 6] = dvMain[0][Unit.UnitName].ToString();
msArray[k, 7] = dvMain[0][IndicatorClassifications.ICName].ToString();
k += 1;
}
}
k += 1;
bSupressArea = false;
}
//*** Set Formula
//*** Set the formula for User Defined Formula
// Total no of data columns
iDataCol = 3 + this.SelectedNids.Count;
sChageValue = formulaText; //txt6_1_Formula.Text;
for (i = 0; i <= sIdentifierChar.Count - 1; i++)
{
sChageValue = sChageValue.Replace(sIdentifierChar[i], "_" + sIdentifierChar[i] + "_");
}
for (i = 0; i <= this.SelectedNids.Count - 1; i++)
{
TempFillFromString = SummarySheet.Cells[iCtrFillFrom, 2 + i].Address.Replace("$", "");
// sChageValue = sChageValue.Replace("_" + lv6_1_Available.Items(selNId2[i]).SubItems(2).Text + "_", TempFillFromString);
sChageValue = sChageValue.Replace("_" + this.Step1SelectionDataTable.Rows[Convert.ToInt32(selNId2[i])][2].ToString() + "_", TempFillFromString);
}
sChageValue = "IF(TYPE(" + sChageValue + ")=16,\"\",ROUND(" + sChageValue + "," + SummarySheet.Cells[6, 1].Address + "))";
sAddress = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address;
SummarySheet.Range[sAddress].Formula = "= " + sChageValue;
TempFillFromString = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address.Replace("$", "");
oRngStart = SummarySheet.Range[TempFillFromString];
TempFillToString = SummarySheet.Cells[iCtrFillTo, iDataCol - 1].Address.Replace("$", "");
if (TempFillFromString != TempFillToString)
{
sMaxRange = TempFillFromString + ":" + TempFillToString;
oRngTotal = SummarySheet.Range[sMaxRange];
this.AutoFillRange(oRngStart, oRngTotal);
}
//*** Setting the Font
SummarySheet.Range[0, 0, iCtrFillTo, iDataCol + 3].Font.Name = this.FontName;
SummarySheet.Range[0, 0, iCtrFillTo, iDataCol + 3].Font.Size = (float)this.FontSize;
SummarySheet.Range[6, 0, iCtrFillTo, iDataCol + 3].Columns.AutoFit();
SummarySheet.Cells[0, 0].Font.Bold = true;
SummarySheet.Cells[0, 0].Font.Italic = true;
SummarySheet.Cells[0, 0].Font.Size = this.TitleFontSize;
//*** Progress
this.RaiseEventSetProgress(45);
//*********************************** S H O W L O G **************************************
LogSheet.Cells[0, 0].Value = LangStrings.VAR_LOG_NAME;
LogSheet.Cells[2, 0].Value = LangStrings.sMODULE;
LogSheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME;
LogSheet.Cells[3, 0].Value = LangStrings.DATE_TIME;
LogSheet.Cells[3, 1].Value = this.TimeStamp;
LogSheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME;
LogSheet.Cells[4, 1].Value = "Log_User Defined Formula (" + this.TimeStamp + ").xls";
iCtrFillFrom = 8;//9;
//*** Set Data
LogSheet.Range[iCtrFillFrom, 0, iCtrFillFrom + msArray.GetLength(0) - 1, 7].Value = msArray;
//*** Autofit
LogSheet.Range[iCtrFillFrom - 2, 0, iCtrFillFrom + msArray.GetLength(0) - 1, 7].Columns.AutoFit();
//*** Set Font
LogSheet.Range[0, 0, iCtrFillFrom + msArray.GetLength(0) - 1, 7].Font.Name = this.FontName;
LogSheet.Range[0, 0, iCtrFillFrom + msArray.GetLength(0) - 1, 7].Font.Size = (float)this.FontSize;
LogSheet.Cells[0, 0].Font.Bold = true;
LogSheet.Cells[0, 0].Font.Italic = true;
LogSheet.Cells[0, 0].Font.Size = this.TitleFontSize;
//******************************************************************************************
this.RaiseEventSetProgress(100);
this.DIExcel.GetWorksheet(0).Cells[0, 0].Activate();
DIExcel.SaveAs(TempLogFilePath);
//*** hide progress bar
this.RaiseEventHideProgressBar();
SummarySheet = null;
LogSheet = null;
dvMain.RowFilter = string.Empty;
}
catch (Exception ex)
{
}
finally
{
this.RaiseEventSetCursor(true);
}
thisThread.CurrentCulture = originalCulture;
this.RaiseEventCalculateStepCompleted("pnl6_3");
}
/// <summary>
/// Perform Transform Unit Calculations
/// </summary>
/// <param name="step2UnitText">Name Of the Unit in step 2</param>
/// <param name="step2ConversionTable"></param>
public void FillTransformUnitSummaryLog(String step2UnitText, DataTable step2ConversionTable)
{
try
{
this.RaiseEventSetCursor(false);
IWorksheet SummarySheet;
IWorksheet LogSheet;
int i = 0;
int iAreaCount = 0;
int iCtrFillFrom = 0;
int iCtrFillTo = 0;
int iDataCol = 0;
StringCollection oSubgroup = new StringCollection();
string sTempFillFromString = string.Empty;
string sTempFillToString = string.Empty;
// Initialize Progress Bar
this.RaiseEventSetProgress(1);
// Copy Log file to temp location
if (File.Exists(this.LogFilePath))
{
File.Copy(this.LogFilePath, this.TempLogFilePath, true);
System.IO.File.SetAttributes(TempLogFilePath, FileAttributes.Normal);
}
ApplyLanguageSettings();
// Opening and getting workbook
this.DIExcel = new DIExcel(TempLogFilePath);
SummarySheet = DIExcel.GetWorksheet(0);
LogSheet = DIExcel.GetWorksheet(1);
SummarySheet.Name = LangStrings.SUMMARY;
LogSheet.Name = LangStrings.LOG;
//*************************** SHOW INDEX ******************************************
this.RaiseEventSetProgress(10);
//data view with all filters applied
DataView dvMain = this.PresentationData;
// Apply Data Filters using selected Ind NId
if (dvMain.RowFilter == "")
{
dvMain.RowFilter = Indicator.IndicatorNId + " = " + this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId];
}
else
{
dvMain.RowFilter += " AND " + Indicator.IndicatorNId + " = " + this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]; ;
}
dvMain.Sort = Area.AreaID + " ASC, " + Unit.UnitName + " Asc";
StringDictionary ColUnit = new StringDictionary();
//foreach (DataRowView Drv in (DataView)dg5_2_Conversion.DataSource)
foreach (DataRowView Drv in step2ConversionTable.DefaultView)
{
ColUnit.Add(Drv[Unit.UnitNId].ToString(), Drv["Factor"].ToString());
}
msDataArray = new string[dvMain.Count, 6];
string[,] msSummaryArray = new string[dvMain.Count + 2, 9];
msSummaryArray[0, 0] = LangStrings.AREAID;
msSummaryArray[0, 1] = LangStrings.AREANAME;
msSummaryArray[0, 2] = LangStrings.UNIT;
msSummaryArray[0, 3] = LangStrings.DATAVALUE;
msSummaryArray[0, 4] = LangStrings.CONVERSION_FACTOR;
msSummaryArray[0, 5] = step2UnitText;// txt5_2_Unit.Text;
msSummaryArray[0, 6] = LangStrings.SUBGROUP;
msSummaryArray[0, 7] = LangStrings.TIME;
msSummaryArray[0, 8] = LangStrings.SOURCE;
string PrevAreaId = "";
i += 2;
foreach (DataRowView Drv in dvMain)
{
//*** Fill Datavalue for Numerator Indicator against each AreaID
if (PrevAreaId != Drv[Area.AreaID].ToString())
{
iAreaCount += 1;
PrevAreaId = Drv[Area.AreaID].ToString();
}
msSummaryArray[i, 0] = Drv[Area.AreaID].ToString();
msSummaryArray[i, 1] = Drv[Area.AreaName].ToString();
msSummaryArray[i, 2] = Drv[Unit.UnitName].ToString();
msSummaryArray[i, 3] = Drv[Data.DataValue].ToString();
msSummaryArray[i, 4] = ColUnit[Drv[Unit.UnitNId].ToString()].ToString();
msSummaryArray[i, 6] = Drv[SubgroupVals.SubgroupVal].ToString();
if (oSubgroup.Contains(Drv[SubgroupVals.SubgroupValNId].ToString()) == false)
{
oSubgroup.Add(Drv[SubgroupVals.SubgroupValNId].ToString());
}
msSummaryArray[i, 7] = Drv[Timeperiods.TimePeriod].ToString();
msSummaryArray[i, 8] = Drv[IndicatorClassifications.ICName].ToString();
msDataArray[i - 2, 0] = Drv[Timeperiods.TimePeriod].ToString();
msDataArray[i - 2, 1] = Drv[Area.AreaID].ToString();
msDataArray[i - 2, 2] = Drv[Area.AreaName].ToString();
msDataArray[i - 2, 4] = Drv[SubgroupVals.SubgroupVal].ToString();
msDataArray[i - 2, 5] = Drv[IndicatorClassifications.ICName].ToString();
i += 1;
}
ColUnit = null;
string[,] msLogArray = new string[dvMain.Count + iAreaCount + 2, 8];
//*** Data Array for Log Sheet
msLogArray[0, 0] = LangStrings.AREAID;
msLogArray[0, 1] = LangStrings.AREANAME;
msLogArray[0, 2] = LangStrings.INDICATOR;
msLogArray[0, 3] = LangStrings.SUBGROUP;
msLogArray[0, 4] = LangStrings.TIME;
msLogArray[0, 5] = LangStrings.DATAVALUE;
msLogArray[0, 6] = LangStrings.UNIT;
msLogArray[0, 7] = LangStrings.SOURCE;
PrevAreaId = "";
i = 1;
dvMain.GetEnumerator().Reset();
foreach (DataRowView Drv in dvMain)
{
if (PrevAreaId != Drv[Area.AreaID].ToString())
{
i += 1;
PrevAreaId = Drv[Area.AreaID].ToString();
msLogArray[i, 0] = Drv[Area.AreaID].ToString();
msLogArray[i, 1] = Drv[Area.AreaName].ToString();
}
msLogArray[i, 2] = Drv[Indicator.IndicatorName].ToString();
msLogArray[i, 3] = Drv[SubgroupVals.SubgroupVal].ToString();
msLogArray[i, 4] = Drv[Timeperiods.TimePeriod].ToString();
msLogArray[i, 5] = Drv[Data.DataValue].ToString();
msLogArray[i, 6] = Drv[Unit.UnitName].ToString();
msLogArray[i, 7] = Drv[IndicatorClassifications.ICName].ToString();
i += 1;
}
//SubgroupNId in Step 5 - Transform Unit
//*** Special Handling for Subgroup in Transform Unit
this.DESheetInformation.Subgroup = new string[oSubgroup.Count];
this.DESheetInformation.SubgroupGUID = new string[oSubgroup.Count];
for (i = 0; i <= oSubgroup.Count - 1; i++)
{
//-- Using DAL library for getting dataView
string sSql;
sSql = this.DBQueries.Subgroup.GetSubgroupVals(Lib.DI_LibDAL.Queries.FilterFieldType.NId, oSubgroup[i].ToString());
dvMain = this.DBConnection.ExecuteDataTable(sSql).DefaultView;
this.DESheetInformation.Subgroup[i] = dvMain[0][SubgroupVals.SubgroupVal].ToString();
if (dvMain.Count > 0)
{
this.DESheetInformation.SubgroupGUID[i] = dvMain[0][SubgroupVals.SubgroupValGId].ToString();
}
else
{
this.DESheetInformation.SubgroupGUID[i] = Guid.NewGuid().ToString();
}
}
SummarySheet.Cells[0, 0].Value = LangStrings.VAR_WIZARD_NAME;
SummarySheet.Cells[2, 0].Value = LangStrings.sMODULE;
SummarySheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME;
SummarySheet.Cells[3, 0].Value = LangStrings.DATE_TIME;
SummarySheet.Cells[3, 1].Value = this.TimeStamp;
SummarySheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME;
SummarySheet.Cells[4, 1].Value = "Log_Transform Unit (" + this.TimeStamp + ").xls";
SummarySheet.Cells[6, 0].Value = LangStrings.DECIMALPLACES;
SummarySheet.Cells[6, 1].Value = this.DecimalUpDownControlValue;// nud5_3_Decimals.Value;
SummarySheet.Cells[7, 2].Value = Step1SelectionDataTable.Rows[0][0].ToString();// lv5_1_Selected.Items(0).SubItems(0).Text;
this.RaiseEventSetProgress(25);
iCtrFillTo = 8 + msSummaryArray.GetLength(0) - 1;
iDataCol = 9;
//*** Set Data
SummarySheet.Range[8, 0, iCtrFillTo, iDataCol - 1].Value = msSummaryArray;
//*** Set Alignment
SummarySheet.Range[8, 0, iCtrFillTo, iDataCol - 1].Columns.AutoFit();
SummarySheet.Range[7, 0, iCtrFillTo, iDataCol - 1].HorizontalAlignment = HAlign.Left;// -4131;
//xlLeft
SummarySheet.Range[8, 3, iCtrFillTo, 5].HorizontalAlignment = HAlign.Right; //xlRight
this.RaiseEventSetProgress(35);
//*** Set Formula for Transform Unit
string sChageValue;
string sAddress;
string sMinRange;
string sMaxRange;
IRange oRngStart;
IRange oRngTotal;
string TempString = string.Empty;
iCtrFillFrom = 10;
iDataCol = 6;
sChageValue = "";
sTempFillFromString = SummarySheet.Cells[iCtrFillFrom, 3].Address.Replace("$", "");
TempString = SummarySheet.Cells[iCtrFillFrom, 4].Address.Replace("$", "");
sChageValue = "ROUND(" + sTempFillFromString + "*" + TempString + "," + SummarySheet.Cells[6, 1].Address + ")";
sAddress = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address;
SummarySheet.Range[sAddress].Formula = "= " + sChageValue;
sTempFillFromString = SummarySheet.Cells[iCtrFillFrom, iDataCol - 1].Address.Replace("$", "");
oRngStart = SummarySheet.Range[sTempFillFromString];
sTempFillToString = SummarySheet.Cells[iCtrFillTo, iDataCol - 1].Address.Replace("$", "");
if (sTempFillFromString != sTempFillToString)
{
sMaxRange = sTempFillFromString + ":" + sTempFillToString;
oRngTotal = SummarySheet.Range[sMaxRange];
this.AutoFillRange(oRngStart, oRngTotal);
}
//*** Set Font
iDataCol = 9;
SummarySheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Name = this.FontName;
SummarySheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Size = (float)this.FontSize;
SummarySheet.Cells[0, 0].Font.Bold = true;
SummarySheet.Cells[0, 0].Font.Italic = true;
SummarySheet.Cells[0, 0].Font.Size = this.TitleFontSize;
SummarySheet.Cells[7, 2].Font.Bold = true;
//*** Progress
this.RaiseEventSetProgress(45);
//*********************************** S H O W L O G **************************************
LogSheet.Cells[0, 0].Value = LangStrings.VAR_LOG_NAME;
LogSheet.Cells[2, 0].Value = LangStrings.sMODULE;
LogSheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME;
LogSheet.Cells[3, 0].Value = LangStrings.DATE_TIME;
LogSheet.Cells[3, 1].Value = this.TimeStamp;
LogSheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME;
LogSheet.Cells[4, 1].Value = "Log_Transform Unit (" + this.TimeStamp + ").xls";
iCtrFillFrom = 8;
iCtrFillTo = iCtrFillFrom + msLogArray.GetLength(0) - 1;
iDataCol = 8;
//*** Set Data
LogSheet.Range[iCtrFillFrom, 0, iCtrFillTo, iDataCol - 1].Value = msLogArray;
//*** Set Alignment
LogSheet.Range[iCtrFillFrom, 0, iCtrFillTo, iDataCol - 1].Columns.AutoFit();
LogSheet.Range[iCtrFillFrom, 4, iCtrFillTo, 4].HorizontalAlignment = HAlign.Left;
//xlLeft 'Time column
//*** Set Font
LogSheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Name = this.FontName;
LogSheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Size = (float)this.FontSize;
LogSheet.Cells[0, 0].Font.Bold = true;
LogSheet.Cells[0, 0].Font.Italic = true;
LogSheet.Cells[0, 0].Font.Size = this.TitleFontSize;
//******************************************************************************************
this.RaiseEventSetProgress(100);
// ExcelWorkBook.Sheets(1).Cells[1, 1].Activate();
//*** Bring Focus to top of the page
DIExcel.GetWorksheet(0).Cells[0, 0].Activate();
DIExcel.Save();
//*** hide progress bar
this.RaiseEventHideProgressBar();
SummarySheet = null;
LogSheet = null;
this.RaiseEventCalculateStepCompleted("pnl5_4");
}
catch (Exception ex)
{
}
finally
{
this.RaiseEventSetCursor(true);
}
}
/// <summary>
/// Perform SubTotal Calculations
/// </summary>
/// <param name="userPreference"></param>
/// <param name="lastAreaLevel">Last area Level ,It is milastAreaLevel in UI application </param>
/// <param name="Step2SelectionDataTable">Data Table having Step 2 selections of area level</param>
public void FillSubtotalSummaryLog(UserPreference.UserPreference userPreference, Int32 lastAreaLevel, DataTable Step2SelectionDataTable)
{
try
{
//Required Information: UserPreferance
//milastAreaLevel from set level method
//Step 2 selections of area level
this.RaiseEventSetCursor(false);
IWorksheet SummarySheet;
IWorksheet LogSheet;
int i = 0;
int j = 0;
int k = 0;
int iAreaCount = 0;
int iCtrFillFrom = 0;
int iCtrFillTo = 0;
int iDataCol = 0;
string sOrgRowFilter = "";
string Str = string.Empty;
DataView dvSubgroup;
//*** Initialize Progress Bar
this.RaiseEventSetProgress(1);
// Copy Log to temp location
if (File.Exists(this.LogFilePath))
{
File.Copy(this.LogFilePath, this.TempLogFilePath, true);
System.IO.File.SetAttributes(TempLogFilePath, FileAttributes.Normal);
}
ApplyLanguageSettings();
// Opening and getting workbook
this.DIExcel = new DIExcel(TempLogFilePath);
SummarySheet = this.DIExcel.GetWorksheet(0);
LogSheet = this.DIExcel.GetWorksheet(1);
SummarySheet.Name = LangStrings.SUMMARY;
LogSheet.Name = LangStrings.LOG;
//*************************** SHOW INDEX ******************************************
this.RaiseEventSetProgress(10);
// Get presentation data
DataView dvMain = this.PresentationData;
//Get subgroup details using user selection
Str = this.DBQueries.Calculates.Indicator_Subgroup_Selected(true, userPreference.UserSelection, userPreference.Database.SelectedConnectionDetail.ServerType);
dvSubgroup = this.DBConnection.ExecuteDataTable(Str).DefaultView;
////Get area details using user selection
Str = this.DBQueries.Area.GetArea(Lib.DI_LibDAL.Queries.FilterFieldType.NId, userPreference.UserSelection.AreaNIds);
DataView dvArea = this.DBConnection.ExecuteDataTable(Str).DefaultView;
//Getting Log array. //*** Data Array for Log Sheet 'dvArea.Count for blank rows
string[,] msLogArray = new string[dvMain.Count + dvArea.Count + 2, 8];
//Use Filter Indicator NId form indicator selected in step 1
dvSubgroup.RowFilter = Indicator.IndicatorNId + " = " + Convert.ToInt32(this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]);// lv4_1_Selected.Items(0).Tag;
StringCollection oSubgroup = new StringCollection();
StringCollection oSubgroupVal = new StringCollection();
StringCollection oSubgroupGId = new StringCollection();
for (k = 0; k <= dvSubgroup.Count - 1; k++)
{
// add subgroup and subgroup gid into subgroup collection
if (oSubgroup.Count > 0)
{
//oSubgroup.Add((string)dvSubgroup[k]["Subgroup_Val_NId"]) give error unable to convert int to string
if (oSubgroup.Contains(Convert.ToInt32(dvSubgroup[k][SubgroupVals.SubgroupValNId]).ToString()) == false)
{
//oSubgroup.Add((string)dvSubgroup[k]["Subgroup_Val_NId"]);
oSubgroup.Add(Convert.ToInt32(dvSubgroup[k][SubgroupVals.SubgroupValNId]).ToString());
oSubgroupVal.Add((string)dvSubgroup[k][SubgroupVals.SubgroupVal]);
}
}
else
{
oSubgroup.Add(Convert.ToInt32(dvSubgroup[k][SubgroupVals.SubgroupValNId]).ToString());
oSubgroupVal.Add((string)dvSubgroup[k][SubgroupVals.SubgroupVal]);
}
}
string PrevAreaId = "";
int iAreaParentNId = 0;
ArrayList ArrAreaNId = new ArrayList();
Hashtable AreaCol = new Hashtable();
string sSource = string.Empty;
string sTimePeriod = string.Empty;
//* Data Filters
if (dvMain.RowFilter == "")
{
sOrgRowFilter = "";
}
else
{
sOrgRowFilter = dvMain.RowFilter + " AND ";
}
// Add selected area indicator and last area level in filter. So if area level is 3
dvMain.RowFilter = sOrgRowFilter + Indicator.IndicatorNId + " = " + Convert.ToInt32(this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]) + " AND Area_Level=" + lastAreaLevel;
dvMain.Sort = Timeperiods.TimePeriod + " DESC"; // GetMostRecent Data
foreach (DataRowView drv in dvMain)
{
//*** Fill Datavalue for Numerator Indicator against each AreaID
dvArea.RowFilter = Area.AreaNId + "=" + Convert.ToInt32(drv[Area.AreaNId]);
iAreaParentNId = Convert.ToInt32(dvArea[0][Area.AreaParentNId]);
dvArea.RowFilter = Area.AreaNId + "=" + iAreaParentNId;
if (AreaCol.ContainsKey(iAreaParentNId) == false)
{
ArrAreaNId.Add(iAreaParentNId);
AreaCol.Add(iAreaParentNId, 0);
AreaCol[iAreaParentNId] = new Hashtable();
for (k = 0; k <= oSubgroup.Count - 1; k++)
{
((Hashtable)AreaCol[iAreaParentNId]).Add((string)oSubgroup[k], 0);
}
}
((Hashtable)AreaCol[iAreaParentNId])[(int)drv[SubgroupVals.SubgroupValNId]] = Convert.ToDouble(((Hashtable)AreaCol[iAreaParentNId])[(int)drv["Subgroup_Val_NId"]]) + Convert.ToDouble(drv["Data_Value"]);
//((Hashtable)AreaCol[iAreaParentNId])[(string)drv["Subgroup_Val_NId"]] = (int)((Hashtable)AreaCol[iAreaParentNId](string)drv["Subgroup_Val_NId"]] + (int)drv["Data_Value"];
sSource = drv[IndicatorClassifications.ICName].ToString();
sTimePeriod = drv[Timeperiods.TimePeriod].ToString();
}
//*** BugFix 10 Aug 2006 Skipping of areas without child nodes at last level
dvMain.RowFilter = sOrgRowFilter + Indicator.IndicatorNId + " = " + Convert.ToInt32(this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]) + " AND Area_Level=" + Convert.ToString(lastAreaLevel - 1);
dvMain.Sort = Timeperiods.TimePeriod + " DESC";
//*** GetMostRecent Data
foreach (DataRowView drv in dvMain)
{
//*** Fill Datavalue for Numerator Indicator against each AreaID
if (AreaCol.Count > 0)
{
if (AreaCol.ContainsKey(drv[Area.AreaNId]) == false)
{
ArrAreaNId.Add(drv[Area.AreaNId]);
AreaCol.Add(drv[Area.AreaNId], 0);
AreaCol[drv[Area.AreaNId]] = new Hashtable();
for (k = 0; k <= oSubgroup.Count - 1; k++)
{
((Hashtable)AreaCol[drv[Area.AreaNId]]).Add((string)oSubgroup[k], 0);
}
}
}
else
{
ArrAreaNId.Add(drv[Area.AreaNId]);
AreaCol.Add(drv[Area.AreaNId], 0);
AreaCol[drv[Area.AreaNId]] = new Hashtable();
for (k = 0; k <= oSubgroup.Count - 1; k++)
{
((Hashtable)AreaCol[drv[Area.AreaNId]]).Add((string)oSubgroup[k], 0);
}
}
((Hashtable)AreaCol[drv[Area.AreaNId]])[(int)drv[SubgroupVals.SubgroupValNId]] = Convert.ToDouble(drv["Data_Value"]);
}
ArrayList ArrAreaNIdOrg = new ArrayList();
ArrAreaNIdOrg = ArrAreaNId;
Hashtable AreaColOrg = new Hashtable();
AreaColOrg = AreaCol;
//----------------------- Retrive the index count before declaring array --------------
i = 2;
object ColItem;
int iDataArrCtr = 0;
for (j = lastAreaLevel - 1; j >= 1; j += -1)
{
//?? 1 may not be the top level
Hashtable AreaColTemp = new Hashtable();
ArrayList ArrAreaNIdTemp = new ArrayList();
for (int l = 0; l <= ArrAreaNId.Count - 1; l++)
{
ColItem = AreaCol[ArrAreaNId[l]];
dvArea = null;
string sSql = string.Empty;
sSql = this.DBQueries.Area.GetArea(Lib.DI_LibDAL.Queries.FilterFieldType.NId, ((int)ArrAreaNId[l]).ToString());
dvArea = this.DBConnection.ExecuteDataTable(sSql).DefaultView;
iAreaParentNId = Convert.ToInt32(dvArea[0][Area.AreaParentNId]);
if (AreaColTemp.Count == 0 || AreaColTemp.ContainsKey(iAreaParentNId) == false)
{
ArrAreaNIdTemp.Add(iAreaParentNId);
AreaColTemp.Add(iAreaParentNId, 0);
AreaColTemp[iAreaParentNId] = new Hashtable();
}
if (Step2SelectionDataTable.Rows[j - 1]["Checked"].ToString().Trim().Length > 0 && Convert.ToBoolean(Step2SelectionDataTable.Rows[j - 1]["Checked"]) == true)
{
for (k = 0; k <= oSubgroup.Count - 1; k++)
{
iDataArrCtr += 1;
}
i += 1;
}
}
dvMain.RowFilter = sOrgRowFilter + Indicator.IndicatorNId + " = " + Convert.ToInt32(this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]) + " AND Area_Level=" + Convert.ToString(j - 1);
dvMain.Sort = Timeperiods.TimePeriod + " DESC";
//*** GetMostRecent Data
foreach (DataRowView drv in dvMain)
{
//*** Fill Datavalue for Numerator Indicator against each AreaID
if (AreaColTemp.Count == 0 || AreaColTemp.ContainsKey(drv[Area.AreaNId]) == false)
{
ArrAreaNIdTemp.Add(Convert.ToInt32(drv[Area.AreaNId]));
AreaColTemp.Add(Convert.ToInt32(drv[Area.AreaNId]), 0);
AreaColTemp[drv[Area.AreaNId]] = new Hashtable();
for (k = 0; k <= oSubgroup.Count - 1; k++)
{
((Hashtable)AreaColTemp[drv[Area.AreaNId]]).Add((string)oSubgroup[k], 0);
}
}
}
ArrAreaNId = ArrAreaNIdTemp;
AreaCol = AreaColTemp;
}
ArrAreaNId = ArrAreaNIdOrg;
AreaCol = AreaColOrg;
//-----------------------------------------------------------------------------------
//--------------------- Declare Array -----------------------------------------------
string[,] msSummaryArray = new string[i - 1 + 3, 1 + oSubgroup.Count + 1];
string[,] msTempDataArray = new string[iDataArrCtr, 6];
msSummaryArray[0, 0] = LangStrings.AREAID;
msSummaryArray[0, 1] = LangStrings.AREANAME;
for (k = 0; k <= oSubgroup.Count - 1; k++)
{
dvSubgroup.RowFilter = SubgroupVals.SubgroupValNId + " =" + Convert.ToInt32(oSubgroup[k]);
msSummaryArray[0, 2 + k] = dvSubgroup[0][SubgroupVals.SubgroupVal].ToString();
}
i = 2;
iDataArrCtr = 0;
for (j = lastAreaLevel - 1; j >= 1; j += -1)
{
//??? 1 may not be the top level
Hashtable AreaColTemp = new Hashtable();
ArrayList ArrAreaNIdTemp = new ArrayList();
for (int l = 0; l <= ArrAreaNId.Count - 1; l++)
{
ColItem = AreaCol[ArrAreaNId[l]];
dvArea = null;
string sSql = string.Empty;
sSql = this.DBQueries.Area.GetArea(Lib.DI_LibDAL.Queries.FilterFieldType.NId, ArrAreaNId[l].ToString());
dvArea = this.DBConnection.ExecuteDataTable(sSql).DefaultView;
//if (lv4_2_Level.Items(j - 1).Checked)
if (Step2SelectionDataTable.Rows[j - 1]["Checked"].ToString().Trim().Length > 0 && Convert.ToBoolean(Step2SelectionDataTable.Rows[j - 1]["Checked"]) == true)
{
msSummaryArray[i, 0] = dvArea[0][Area.AreaID].ToString();
msSummaryArray[i, 1] = dvArea[0][Area.AreaName].ToString();
}
// Get Parent area Nid
iAreaParentNId = Convert.ToInt32(dvArea[0][Area.AreaParentNId]);
if (AreaColTemp.Count == 0 || AreaColTemp.ContainsKey(iAreaParentNId) == false)
{
ArrAreaNIdTemp.Add(iAreaParentNId);
AreaColTemp.Add(iAreaParentNId, 0);
AreaColTemp[iAreaParentNId] = new Hashtable();
for (k = 0; k <= oSubgroup.Count - 1; k++)
{
((Hashtable)AreaColTemp[iAreaParentNId]).Add(Convert.ToInt32(oSubgroup[k]), 0);
}
}
//if (lv4_2_Level.Items(j - 1).Checked)
if (Step2SelectionDataTable.Rows[j - 1]["Checked"].ToString().Trim().Length > 0 && Convert.ToBoolean(Step2SelectionDataTable.Rows[j - 1]["Checked"]) == true)
{
for (k = 0; k <= oSubgroup.Count - 1; k++)
{
msSummaryArray[i, k + 2] = Convert.ToString(((Hashtable)AreaCol[ArrAreaNId[l]])[(Convert.ToInt32(oSubgroup[k]))]);
msTempDataArray[iDataArrCtr, 0] = sTimePeriod;
msTempDataArray[iDataArrCtr, 1] = dvArea[0][Area.AreaID].ToString();
msTempDataArray[iDataArrCtr, 2] = dvArea[0][Area.AreaName].ToString();
msTempDataArray[iDataArrCtr, 3] = msSummaryArray[i, k + 2].ToString();
msTempDataArray[iDataArrCtr, 4] = (string)oSubgroupVal[k];
msTempDataArray[iDataArrCtr, 5] = sSource;
iDataArrCtr += 1;
//*** Bugfix 19 Apr 2007 Error for very high datavalue due to usage of CINT
//CType(AreaColTemp.Item(iAreaParentNId), Hashtable).Item(CStr(oSubgroup.Item(k))) =
//(CType(AreaColTemp.Item(iAreaParentNId), Hashtable).Item(CStr(oSubgroup.Item(k)))) +
//(CType(AreaCol.Item(ArrAreaNId(l)), Hashtable).Item(CStr(oSubgroup.Item(k))))
((Hashtable)AreaColTemp[iAreaParentNId])[Convert.ToInt32(oSubgroup[k])] =
Convert.ToDouble(((Hashtable)AreaColTemp[iAreaParentNId])[Convert.ToInt32(oSubgroup[k])]) + Convert.ToDouble(((Hashtable)AreaCol[ArrAreaNId[l]])[Convert.ToInt32(oSubgroup[k])]);
}
i += 1;
}
}
//*** BugFix 10 Aug 2006 Skipping of areas without child nodes at last level
//dvMain.RowFilter = sOrgRowFilter + "Indicator_NId = " + lv4_1_Selected.Items(0).Tag + " AND Area_Level=" + j - 1;
dvMain.RowFilter = sOrgRowFilter + Indicator.IndicatorNId + " = "
+ Convert.ToInt32(this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]) + " AND " + Area_Level.AreaLevel + " =" + Convert.ToString(j - 1);
dvMain.Sort = Timeperiods.TimePeriod + " DESC";
//*** GetMostRecent Data
foreach (DataRowView DataviewRow in dvMain)
{
//*** Fill Datavalue for Numerator Indicator against each AreaID
if (AreaColTemp.ContainsKey(DataviewRow[Area.AreaNId]) == false)
{
ArrAreaNIdTemp.Add(DataviewRow[Area.AreaNId].ToString());
AreaColTemp.Add(DataviewRow[Area.AreaNId], 0);
AreaColTemp[Convert.ToInt32(DataviewRow[Area.AreaNId])] = new Hashtable();
for (k = 0; k <= oSubgroup.Count - 1; k++)
{
((Hashtable)AreaColTemp[DataviewRow[Area.AreaNId]]).Add((string)oSubgroup[k], 0);
}
}
//*** Overwrite the subtotal achieved through cumulation, if data for an area exists.
((Hashtable)AreaColTemp[DataviewRow[Area.AreaNId]])[Convert.ToInt32(DataviewRow[SubgroupVals.SubgroupValNId])] = Convert.ToDouble(DataviewRow[Data.DataValue]);
}
ArrAreaNId = ArrAreaNIdTemp;
AreaCol = AreaColTemp;
}
//Finish button error - SubTotal
msDataArray = new string[iDataArrCtr, 6];
for (i = 0; i <= iDataArrCtr - 1; i++)
{
for (j = 0; j <= 5; j++)
{
msDataArray[i, j] = msTempDataArray[i, j].ToString();
}
}
msLogArray[0, 0] = LangStrings.AREAID;
msLogArray[0, 1] = LangStrings.AREANAME;
msLogArray[0, 2] = LangStrings.INDICATOR;
msLogArray[0, 3] = LangStrings.SUBGROUP;
msLogArray[0, 4] = LangStrings.TIME;
msLogArray[0, 5] = LangStrings.DATAVALUE;
msLogArray[0, 6] = LangStrings.UNIT;
msLogArray[0, 7] = LangStrings.SOURCE;
PrevAreaId = "";
i = 1;
dvMain.RowFilter = sOrgRowFilter + Indicator.IndicatorNId + " = " + Convert.ToInt32(this.Step1SelectionDataTable.Rows[0][Indicator.IndicatorNId]);// lv4_1_Selected.Items(0).Tag;
dvMain.GetEnumerator().Reset();
dvMain.Sort = " Area_ID";
foreach (DataRowView drv in dvMain)
{
if (PrevAreaId != drv[Area.AreaID].ToString())
{
i += 1;
PrevAreaId = drv[Area.AreaID].ToString();
msLogArray[i, 0] = drv[Area.AreaID].ToString();
msLogArray[i, 1] = drv[Area.AreaName].ToString();
}
msLogArray[i, 2] = drv[Indicator.IndicatorName].ToString();
msLogArray[i, 3] = drv[SubgroupVals.SubgroupVal].ToString();
msLogArray[i, 4] = drv[Timeperiods.TimePeriod].ToString();
msLogArray[i, 5] = drv[Data.DataValue].ToString();
msLogArray[i, 6] = drv[Unit.UnitName].ToString();
msLogArray[i, 7] = drv[IndicatorClassifications.ICName].ToString();
i += 1;
}
//*** Special Handling for Subgroup in SubTotal
this.DESheetInformation.Subgroup = new string[oSubgroup.Count];
this.DESheetInformation.SubgroupGUID = new string[oSubgroup.Count];
for (i = 0; i <= oSubgroup.Count - 1; i++)
{
string sSql = string.Empty;
sSql = this.DBQueries.Subgroup.GetSubgroupVals(Lib.DI_LibDAL.Queries.FilterFieldType.NId, oSubgroup[i].ToString());
dvMain = this.DBConnection.ExecuteDataTable(sSql).DefaultView;
this.DESheetInformation.Subgroup[i] = dvMain[0][SubgroupVals.SubgroupVal].ToString();
if (dvMain.Count > 0)
{
this.DESheetInformation.SubgroupGUID[i] = dvMain[0][SubgroupVals.SubgroupValGId].ToString();
}
else
{
this.DESheetInformation.SubgroupGUID[i] = Guid.NewGuid().ToString();
}
}
SummarySheet.Cells[0, 0].Value = LangStrings.VAR_WIZARD_NAME;
SummarySheet.Cells[2, 0].Value = LangStrings.sMODULE;
SummarySheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME;
SummarySheet.Cells[3, 0].Value = LangStrings.DATE_TIME;
SummarySheet.Cells[3, 1].Value = TimeStamp;
SummarySheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME;
SummarySheet.Cells[4, 1].Value = "Log_Subtotal (" + TimeStamp + ").xls";
SummarySheet.Cells[8, 2].Value = this.DESheetInformation.Indicator;
this.RaiseEventSetProgress(25);
//*** Set Data
iDataCol = 2 + oSubgroup.Count;
iCtrFillFrom = 9; //10;
iCtrFillTo = iCtrFillFrom + msSummaryArray.GetLength(0) - 1;
SummarySheet.Range[iCtrFillFrom, 0, iCtrFillTo, iDataCol - 1].Value = msSummaryArray;
this.RaiseEventSetProgress(35);
//Fill Data Values and Formula for each records
//*** Setting the Font
SummarySheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Name = this.FontName;
SummarySheet.Range[0, 0, iCtrFillTo, iDataCol - 1].Font.Size = (float)this.FontSize;
SummarySheet.Range[iCtrFillFrom, 0, iCtrFillTo, iDataCol - 1].Columns.AutoFit();
//SummarySheet.Range[iCtrFillFrom, 0, iCtrFillTo, iDataCol - 1].Rows.AutoFit();
SummarySheet.Cells[0, 0].Font.Bold = true;
SummarySheet.Cells[0, 0].Font.Italic = true;
SummarySheet.Cells[0, 0].Font.Size = this.TitleFontSize;
SummarySheet.Cells[8, 2].Font.Bold = true;
//*** Progress
this.RaiseEventSetProgress(45);
//*********************************** S H O W L O G **************************************
LogSheet.Cells[0, 0].Value = LangStrings.VAR_LOG_NAME;
LogSheet.Cells[2, 0].Value = LangStrings.sMODULE;
LogSheet.Cells[2, 1].Value = LangStrings.VAR_WIZARD_NAME;
LogSheet.Cells[3, 0].Value = LangStrings.DATE_TIME;
LogSheet.Cells[3, 1].Value = this.TimeStamp;
LogSheet.Cells[4, 0].Value = LangStrings.LOG_FILE_NAME;
LogSheet.Cells[4, 1].Value = "Log_Subtotol (" + this.TimeStamp + ").xls";
iCtrFillFrom = 8;// 9;
//*** Set Data
LogSheet.Range[iCtrFillFrom, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 7].Value = msLogArray;
//*** Autofit
LogSheet.Range[iCtrFillFrom, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 7].Columns.AutoFit();
LogSheet.Range[iCtrFillFrom, 4, iCtrFillFrom + msLogArray.GetLength(0) - 1, 4].HorizontalAlignment = HAlign.Left;// -4131;
//xlLeft 'Time column
//*** Set Font
LogSheet.Range[0, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 7].Font.Name = this.FontName;
LogSheet.Range[0, 0, iCtrFillFrom + msLogArray.GetLength(0) - 1, 7].Font.Size = (float)this.FontSize;
LogSheet.Cells[0, 0].Font.Bold = true;
LogSheet.Cells[0, 0].Font.Italic = true;
LogSheet.Cells[0, 0].Font.Size = this.TitleFontSize;
//******************************************************************************************
this.RaiseEventSetProgress(100);
this.DIExcel.GetWorksheet(0).Cells[0, 0].Activate();
this.DIExcel.Save();
//*** hide progress bar
this.RaiseEventHideProgressBar();
SummarySheet = null;
LogSheet = null;
dvMain.RowFilter = string.Empty;
//goExcel.SetParent(pnl4_3.Handle.ToInt32);
this.RaiseEventCalculateStepCompleted("pnl4_3");
}
catch (Exception ex)
{
}
finally
{
this.RaiseEventSetCursor(true);
}
}
public Util_Excel_GridView()
{
workbook = SpreadsheetGear.Factory.GetWorkbook();
worksheet = workbook.Worksheets[counterPage];
cells = worksheet.Cells;
}
/// <summary>
/// Builds a line graph (in this case typically the X Axis is always the Elapsed Time Column)
/// </summary>
/// <param name="dataWorksheet">The data worksheet.</param>
/// <param name="graphConfig">The graph configuration.</param>
/// <param name="columnNameIndex">Index of the column name.</param>
/// <exception cref="ApplicationException">... Error building graph: [{graphConfig.Name}], Expected cols: [{errList}</exception>
private static void BuildLineGraph(SpreadsheetGear.IWorksheet dataWorksheet, LineGraphBE lineGraphConfig, Dictionary <string, int> columnNameIndex, string pathNameColumnName)
{
SpreadsheetGear.IWorkbook workbook = dataWorksheet.Workbook;
int columnIdx = -1;
int xAxisTargetColumnIdx = -1;
string xAxisColumnName = lineGraphConfig.XAxis.FromColumnName;
List <string> missingColumnNames = new List <string>();
// step 1: find the column we want to target for the XAxis
if (!columnNameIndex.TryGetValue(xAxisColumnName, out xAxisTargetColumnIdx))
{
missingColumnNames.Add(xAxisColumnName);
}
// step 2.1: find the columns we want to target for the YAxis
Dictionary <int, string> yAxisTargetColIdxs = new Dictionary <int, string>();
foreach (string yAxisColumnName in lineGraphConfig.YAxis.FromColumnNames)
{
if (columnNameIndex.TryGetValue(yAxisColumnName, out columnIdx))
{
yAxisTargetColIdxs.Add(columnIdx, yAxisColumnName);
}
else
{
missingColumnNames.Add(yAxisColumnName);
}
}
// step 2.2: find the columns we want to target for the YAxis
Dictionary <int, string> secondaryYAxisTargetColIdxs = new Dictionary <int, string>();
if (lineGraphConfig.SecondaryYAxis != null)
{
foreach (string yAxisColumnName in lineGraphConfig.SecondaryYAxis.FromColumnNames)
{
if (columnNameIndex.TryGetValue(yAxisColumnName, out columnIdx))
{
secondaryYAxisTargetColIdxs.Add(columnIdx, yAxisColumnName);
}
else
{
missingColumnNames.Add(yAxisColumnName);
}
}
}
// step 3: find the columns we want to reference for the Gains
string pidGainsColumnName = lineGraphConfig.Gains?.PIDGains;
string followerGainsColumnName = lineGraphConfig.Gains?.FollowerGains;
string controlModeColumnName = lineGraphConfig.Gains?.ControlMode;
int pidGainsColumnIdx = -1;
int followerGainsColumnIdx = -1;
int controlModeColumnIdx = -1;
int elapsedDeltaColumnIdx = -1;
int targetColumnIdx = -1;
int actualColumnIdx = -1;
int pathNameColumnIdx = -1;
if (!string.IsNullOrEmpty(pidGainsColumnName))
{
if (!columnNameIndex.TryGetValue(pidGainsColumnName, out pidGainsColumnIdx))
{
//missingColumnNames.Add(pidGainsColumnName);
}
}
if (!string.IsNullOrEmpty(followerGainsColumnName))
{
if (!columnNameIndex.TryGetValue(followerGainsColumnName, out followerGainsColumnIdx))
{
missingColumnNames.Add(followerGainsColumnName);
}
}
if (!string.IsNullOrEmpty(controlModeColumnName))
{
if (!columnNameIndex.TryGetValue(controlModeColumnName, out controlModeColumnIdx))
{
//missingColumnNames.Add(controlModeColumnName);
}
}
if (!string.IsNullOrEmpty(lineGraphConfig.XAxis.FromColumnName))
{
if (!columnNameIndex.TryGetValue(lineGraphConfig.XAxis.FromColumnName, out elapsedDeltaColumnIdx))
{
missingColumnNames.Add(lineGraphConfig.XAxis.FromColumnName);
}
}
if (!string.IsNullOrEmpty(lineGraphConfig.CalcAreaDelta?.TargetColumnName))
{
if (!columnNameIndex.TryGetValue(lineGraphConfig.CalcAreaDelta.TargetColumnName, out targetColumnIdx))
{
missingColumnNames.Add(lineGraphConfig.CalcAreaDelta.TargetColumnName);
}
}
if (!string.IsNullOrEmpty(lineGraphConfig.CalcAreaDelta?.ActualColumnName))
{
if (!columnNameIndex.TryGetValue(lineGraphConfig.CalcAreaDelta.ActualColumnName, out actualColumnIdx))
{
missingColumnNames.Add(lineGraphConfig.CalcAreaDelta.ActualColumnName);
}
}
if (!string.IsNullOrEmpty(pathNameColumnName))
{
if (!columnNameIndex.TryGetValue(pathNameColumnName, out pathNameColumnIdx))
{
missingColumnNames.Add(pathNameColumnName);
}
}
//
// stop if any were missing
if (missingColumnNames.Count > 0)
{
string errList = String.Join(",", missingColumnNames);
throw new ApplicationException($"... Error building graph: [{lineGraphConfig.Name}], Expected cols: [{errList}] cannot be found!");
}
// Step 4: add a new worksheet to hold the chart
IWorksheet chartSheet = workbook.Worksheets.Add();
chartSheet.Name = lineGraphConfig.Name;
// Step 5.1: time to build the chart
SpreadsheetGear.Shapes.IShape chartShape = chartSheet.Shapes.AddChart(1, 1, 500, 500);
SpreadsheetGear.Charts.IChart chart = chartShape.Chart;
// working variables
int lastRowIdx = dataWorksheet.UsedRange.RowCount;
IRange xAxisColumn = dataWorksheet.Cells[1, 0, lastRowIdx - 1, 0];
IRange yAxisColumn = null;
ISeries chartSeries = null;
string seriesName = string.Empty;
// Step 5.2: add a chart series for each Y axis column in the config
foreach (var kvp in yAxisTargetColIdxs)
{
seriesName = dataWorksheet.Cells[0, kvp.Key].Text;
yAxisColumn = dataWorksheet.Cells[1, kvp.Key, lastRowIdx - 1, kvp.Key];
chartSeries = chart.SeriesCollection.Add();
chartSeries.XValues = $"={xAxisColumn.ToString()}"; // "Sheet1!$A2:$A200";
chartSeries.Values = yAxisColumn.ToString(); //"Sheet1!$H2:$H200";
switch (lineGraphConfig.ChartTypeOverride)
{
case @"XYScatter":
chartSeries.ChartType = ChartType.XYScatter;
break;
default:
chartSeries.ChartType = ChartType.Line;
break;
}
chartSeries.Name = seriesName;
}
foreach (var kvp in secondaryYAxisTargetColIdxs)
{
seriesName = dataWorksheet.Cells[0, kvp.Key].Text;
yAxisColumn = dataWorksheet.Cells[1, kvp.Key, lastRowIdx - 1, kvp.Key];
chartSeries = chart.SeriesCollection.Add();
chartSeries.XValues = $"={xAxisColumn.ToString()}"; // "Sheet1!$A2:$A200";
chartSeries.Values = yAxisColumn.ToString(); //"Sheet1!$H2:$H200";
chartSeries.AxisGroup = AxisGroup.Secondary;
switch (lineGraphConfig.ChartTypeOverride)
{
case @"XYScatter":
chartSeries.ChartType = ChartType.XYScatter;
break;
default:
chartSeries.ChartType = ChartType.Line;
break;
}
chartSeries.Name = seriesName;
}
// Step 5.3: format the chart title
chart.HasTitle = true;
StringBuilder chartTitle = new StringBuilder();
string pathName = dataWorksheet.Cells[1, pathNameColumnIdx].Text;
chartTitle.AppendLine($"{lineGraphConfig.Name} | Path: [{pathName}]");
// optional add follower gains only if available
if (pidGainsColumnIdx >= 0)
{
chartTitle.AppendLine($"PID Gains: {GetPIDGains(dataWorksheet, pidGainsColumnIdx, controlModeColumnIdx)}");
}
// optional add follower gains only if available
if (followerGainsColumnIdx >= 0)
{
chartTitle.AppendLine($"Follower Gains: {dataWorksheet.Cells[1, followerGainsColumnIdx].Text}");
}
if (lineGraphConfig.CalcAreaDelta != null)
{
(decimal posErr, decimal negErr) = CalcAreaDelta(dataWorksheet, elapsedDeltaColumnIdx, targetColumnIdx, actualColumnIdx, lineGraphConfig.Name);
chartTitle.AppendLine($"Error Area (tot): {posErr:N0} | {negErr:N0}");
}
chart.ChartTitle.Text = chartTitle.ToString();
chart.ChartTitle.Font.Size = 12;
// Step 5.4: format the chart legend
chart.Legend.Position = SpreadsheetGear.Charts.LegendPosition.Bottom;
chart.Legend.Font.Bold = true;
// Step 5.5: format X & Y Axes
IAxis xAxis = chart.Axes[AxisType.Category];
xAxis.HasMinorGridlines = true;
xAxis.HasTitle = true;
if (chart.ChartType == ChartType.Line)
{
// this option not valid on xy graphs
xAxis.TickMarkSpacing = 100; // 10Msec per step * 100 = gidline every second
}
IAxisTitle xAxisTitle = xAxis.AxisTitle;
xAxisTitle.Text = lineGraphConfig.XAxis.AxisTitle;
IAxis yAxis = chart.Axes[AxisType.Value, AxisGroup.Primary];
yAxis.HasTitle = true;
yAxis.TickLabels.NumberFormat = "General";
yAxis.ReversePlotOrder = lineGraphConfig.YAxis.IsYAxisValuesInReverseOrder;
if (lineGraphConfig.YAxis.MajorUnitOverride.HasValue)
{
yAxis.MajorUnit = (double)lineGraphConfig.YAxis.MajorUnitOverride.Value;
}
IAxisTitle yAxisTitle = yAxis.AxisTitle;
yAxisTitle.Text = lineGraphConfig.YAxis.AxisTitle;
}
public static string ProcessLogFile(string logFilePathName, string graphSetName, GraphConfigsBE config)
{
// Activate SpreadsheetGear
SpreadsheetGear.Factory.SetSignedLicense("SpreadsheetGear.License, Type=Trial, Product=BND, Expires=2019-07-27, Company=Tom Bruns, [email protected], Signature=orH+RFO9hRUB8SJXBSWQZJuXP9OfSkV9fLcU9suehfgA#dgunwBK9VssTgnfowKGWaqMNfVgwVetxEWbayzGM1uIA#K");
// Create a new empty workbook in a new workbook set.
SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
// import the csv file
SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.Open(logFilePathName);
// get a reference to the active (only) worksheet
SpreadsheetGear.IWorksheet dataWorksheet = workbook.ActiveWorksheet;
dataWorksheet.Name = System.IO.Path.GetFileNameWithoutExtension(logFilePathName);
// freeze 1st row & 1st column(to make scrolling more user friendly)
dataWorksheet.WindowInfo.ScrollColumn = 0;
dataWorksheet.WindowInfo.SplitColumns = 1;
dataWorksheet.WindowInfo.ScrollRow = 0;
dataWorksheet.WindowInfo.SplitRows = 1;
dataWorksheet.WindowInfo.FreezePanes = true;
// build index of column names
var columnNameXref = BuildColumnNameXref(dataWorksheet);
// find the config for the requested Set of Graphs
GraphSetBE graphSet = config.GraphSets.Where(gs => gs.SetName.ToLower() == graphSetName.ToLower()).FirstOrDefault();
if (graphSet == null)
{
List <string> availableGraphSetNames = config.GraphSets.Select(gs => gs.SetName).ToList();
throw new ApplicationException($"Requested GraphSet: [{graphSetName}], Options: [{String.Join(",", availableGraphSetNames)}]");
}
// do any required conversions on the source data (ex Radians to Degrees)
if (graphSet.AngleConversions != null)
{
foreach (AngleConversionBE angleConversion in graphSet.AngleConversions)
{
PerformAngleConversion(dataWorksheet, angleConversion, columnNameXref);
}
// rebuild column name index
columnNameXref = BuildColumnNameXref(dataWorksheet);
}
// resize column widths to fit header text
dataWorksheet.UsedRange.Columns.AutoFit();
// ====================================
// create any new sheets with a subset of the original columns to make analysis easier
// ====================================
foreach (NewSheetBE newSheet in graphSet.NewSheets)
{
BuildNewSheet(dataWorksheet, newSheet, columnNameXref);
}
string pathNameColumnName = graphSet.PathNameColumnName;
// ====================================
// build a new line graph for each one in the selected graphset
// ====================================
foreach (LineGraphBE lineGraph in graphSet.LineGraphs)
{
BuildLineGraph(dataWorksheet, lineGraph, columnNameXref, pathNameColumnName);
}
// ====================================
// build a new XY graph for each one in the selected graphset
// fyi: these were separated because they require slightly different config data structures
// ====================================
foreach (XYGraphBE xyGraph in graphSet.XYGraphs)
{
BuildXYGraph(dataWorksheet, xyGraph, columnNameXref, pathNameColumnName);
}
// ====================================
// build a new bar graph for each one in the selected graphset
// ====================================
foreach (BarGraphBE barGraph in graphSet.BarGraphs)
{
BuildBarGraph(dataWorksheet, barGraph, columnNameXref, pathNameColumnName);
}
// ====================================
// build a new histogram for each one in the selected graphset
// ====================================
foreach (HistogramBE histogram in graphSet.Histograms)
{
BuildHistogram(dataWorksheet, histogram, columnNameXref, pathNameColumnName);
}
// save the workbook
string pathName = GetCellValue <string>(dataWorksheet, graphSet.PathNameColumnName, 1, columnNameXref);
string folderPathName = System.IO.Path.GetDirectoryName(logFilePathName);
string fileName = System.IO.Path.GetFileNameWithoutExtension(logFilePathName);
fileName = (!string.IsNullOrEmpty(pathName)) ? $"{fileName}_{pathName}" : fileName;
fileName = System.IO.Path.ChangeExtension(fileName, @".xlsx");
string xlsFilePathName = System.IO.Path.Combine(folderPathName, fileName);
workbook.SaveAs(xlsFilePathName, FileFormat.OpenXMLWorkbook);
return(xlsFilePathName);
}