/// <summary>Converts a Worksheet data to DataTable in Dictionary</summary>
/// <param name="path"></param> <param1 name="boolean"></param>
/// <returns></returns>
private Dictionary <string, List <TestScenarioMembers> > GetDataTableFromExcel(HttpPostedFileBase tScPath)
{
try
{
Dictionary <string, List <TestScenarioMembers> > testScenarios = new Dictionary <string, List <TestScenarioMembers> >();
using (var pck = new ExcelPackage())
{
using (var stream = tScPath.InputStream)
{
pck.Load(stream);
}
//Get the worksheet in the workbook
var ws = pck.Workbook.Worksheets[2];
int conditionNoCell = 3;
int conditionResultCell = 4;
//loops through the cell values from row 4 and ConditionNo cell
foreach (var firstRowCell in ws.Cells[4, 3, 4, ws.Dimension.End.Column])
{
if (firstRowCell.Text != "" && !firstRowCell.Text.Contains("tempColumn"))
{
string HeaderName = firstRowCell.Text;
for (int rowNum = 6; rowNum <= ws.Dimension.End.Row; rowNum++)
{
string scenarioNo = ws.GetValue(rowNum, 2).ToString();
string conditionNo = ws.GetValue(rowNum, conditionNoCell) == null ? "" : ws.GetValue(rowNum, conditionNoCell).ToString();
string conditionResult = ws.GetValue(rowNum, conditionResultCell) == null ? "" : ws.GetValue(rowNum, conditionResultCell).ToString();
TestScenarioMembers testScenarioMembers = new TestScenarioMembers {
ScenarioNo = scenarioNo, ConditionNo = conditionNo, ConditionResult = conditionResult
};
if (testScenarios.ContainsKey(HeaderName) && testScenarios[HeaderName] != null)
{
testScenarios[HeaderName].Add(testScenarioMembers);
}
else
{
List <TestScenarioMembers> lstScenarioBuilderMembers = new List <TestScenarioMembers>();
lstScenarioBuilderMembers.Add(testScenarioMembers);
testScenarios.Add(HeaderName, lstScenarioBuilderMembers);
}
}
conditionNoCell = conditionNoCell + 2;
conditionResultCell = conditionResultCell + 2;
}
}
return(testScenarios);
}
}
catch (Exception)
{
throw;
}
}
public void AddTestDesign(Dictionary <string, List <TestScenarioMembers> > testScenarios, DataSet transactionMatrixSheets,
ExcelWorksheet ws, int colIndex, int rowIndex, DataTable dtTD, IList <tbl_Transactions> lstHighLevelTransactions, Dictionary <string, List <DataTable> > ruleReferences)
{
try
{
rowIndex++;
int testScenarioIndex = 0;// for scenario description
for (int j = 1; j <= testScenarios.ElementAt(0).Value.Count; j++)
{
foreach (var trans in lstHighLevelTransactions)
{
colIndex = 1;
DataTable transactionMatrixSheet = new DataTable();
DataTable temp = new DataTable();
StringBuilder strBuilder = new StringBuilder();
StringBuilder strAttr = new StringBuilder();
StringBuilder ruleReferenceBuilder = new StringBuilder();
var dictValues = testScenarios[trans.HighLevelTxnDesc];
TestScenarioMembers item = new TestScenarioMembers();
int i = 0;
foreach (var element in dictValues)
{
if (i == (j - 1))
{
if (element.Status == "" && element.ConditionNo != "" && element.ConditionResult != "")
{
item = element;
break;
}
}
i++;
}
if (item != null && item.ConditionResult != null && item.ConditionNo != "" && item.ConditionResult != "")
{
item.Status = "Processed";
var cell = ws.Cells[rowIndex, colIndex];
//Serial No.
cell.Value = j;
ScenarioBuilder.FormatCell(cell);
//cell.Style.WrapText = true;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
transactionMatrixSheet = transactionMatrixSheets.Tables[trans.HighLevelTxnDesc];
DataRow[] result = transactionMatrixSheet.Select("[Test Condition ID] = '" + item.ConditionNo + "'");
string logicalDay = "";
string batchFreq = "";
string gapReference = "";
if (result.Length > 0)
{
foreach (DataRow dr in result)
{
//mandatorily to be in all the fields of test case description
strBuilder.Append("To check " + trans.HighLevelTxnDesc + " with revelant test data");
strBuilder.AppendLine();
if (dr["Business Rule ID"].ToString() != "")
{
List <DataTable> value = new List <DataTable>();
ruleReferences.TryGetValue(trans.HighLevelTxnDesc, out value); //getting the key and value for rule reference table
string[] BusinessRuleIds = dr["Business Rule ID"].ToString().Split(' ', '\n', '\t'); //To split multiple ids that come under the same cell
var conditionNo = dr["Test Condition ID"].ToString();
IEnumerable <DataTable> lastTables = new List <DataTable>();
lastTables = value.Where(e => e.TableName == "Business Rules - " + trans.HighLevelTxnDesc); //under list of tables
DataTable businessRuleTable = new DataTable(); //we get a datatable, and because of above condition, we get datatable at 0th index,
businessRuleTable = lastTables.ElementAt(0); //getting the table from that index
DataRow[] dRow = businessRuleTable.Select("[Test_Cond_ID] = '" + conditionNo + "'"); //fetching the row based on the test condition id
if (dRow.Length > 0)
{
foreach (DataRow dataRow in dRow)//looping the fetched row based on the test condition id
{
foreach (var businessRule in BusinessRuleIds)
{
if (dataRow["Business_Rule_ID"].ToString() != "" && businessRule == dataRow["Business_Rule_ID"].ToString() && conditionNo.ToString() == item.ConditionNo)
{
strBuilder.Append(dataRow.Table.Columns["Business_Rule_ID"].ColumnName + ": " + dataRow["Business_Rule_ID"].ToString());
strBuilder.AppendLine();
strBuilder.Append(dataRow.Table.Columns["Rule Description"].ColumnName + ": " + dataRow["Rule Description"].ToString());
strBuilder.AppendLine();
ruleReferenceBuilder.Append(dataRow["Business_Rule_ID"].ToString());
ruleReferenceBuilder.AppendLine();
}
}
}
}
}
if (dr["Interface ID"].ToString() != "")
{
List <DataTable> value = new List <DataTable>();
ruleReferences.TryGetValue(trans.HighLevelTxnDesc, out value);
string[] interfaceIds = dr["Interface ID"].ToString().Split(' ', '\n', '\t');
var conditionNo = dr["Test Condition ID"].ToString();
IEnumerable <DataTable> lastTables = new List <DataTable>();
lastTables = value.Where(e => e.TableName == "Interface - " + trans.HighLevelTxnDesc);
DataTable businessRuleTable = new DataTable();
businessRuleTable = lastTables.ElementAt(0);
DataRow[] dRow = businessRuleTable.Select("[Test_Cond_ID] = '" + conditionNo + "'");//fetching the row based on the test condition id
if (dRow.Length > 0)
{
foreach (DataRow dataRow in dRow)//looping the fetched row based on the test condition id
{
foreach (var interfaceId in interfaceIds)
{
if (dataRow["Interface_ID"].ToString() != "" && interfaceId == dataRow["Interface_ID"].ToString() && conditionNo.ToString() == item.ConditionNo)
{
strBuilder.Append(dataRow.Table.Columns["Interface_ID"].ColumnName + ": " + dataRow["Interface_ID"].ToString());
strBuilder.AppendLine();
strBuilder.Append(dataRow.Table.Columns["Interface Description"].ColumnName + ": " + dataRow["Interface Description"].ToString());
strBuilder.AppendLine();
ruleReferenceBuilder.Append(dataRow["Interface_ID"].ToString());
ruleReferenceBuilder.AppendLine();
}
}
}
}
}
if (dr["Channel Alert ID"].ToString() != "")
{
List <DataTable> value = new List <DataTable>();
ruleReferences.TryGetValue(trans.HighLevelTxnDesc, out value);
string[] channelAlertIds = dr["Channel Alert ID"].ToString().Split(' ', '\n', '\t');
var conditionNo = dr["Test Condition ID"].ToString();
IEnumerable <DataTable> lastTables = new List <DataTable>();
lastTables = value.Where(e => e.TableName == "Channels and Alerts - " + trans.HighLevelTxnDesc);
DataTable businessRuleTable = new DataTable();
businessRuleTable = lastTables.ElementAt(0);
DataRow[] dRow = businessRuleTable.Select("[Test_Cond_ID] = '" + conditionNo + "'");//fetching the row based on the test condition id
if (dRow.Length > 0)
{
foreach (DataRow dataRow in dRow)//looping the fetched row based on the test condition id
{
foreach (var channelalertId in channelAlertIds)
{
if (dataRow["ChannelAlert_ID"].ToString() != "" && channelalertId == dataRow["ChannelAlert_ID"].ToString() && conditionNo.ToString() == item.ConditionNo)
{
strBuilder.Append(dataRow.Table.Columns["ChannelAlert_ID"].ColumnName + ": " + dataRow["ChannelAlert_ID"].ToString());
strBuilder.AppendLine();
strBuilder.Append(dataRow.Table.Columns["Message Description"].ColumnName + ": " + dataRow["Message Description"].ToString());
strBuilder.AppendLine();
ruleReferenceBuilder.Append(dataRow["ChannelAlert_ID"].ToString());
ruleReferenceBuilder.AppendLine();
}
}
}
}
}
if (dr["Report ID"].ToString() != "")
{
List <DataTable> value = new List <DataTable>();
ruleReferences.TryGetValue(trans.HighLevelTxnDesc, out value);
string[] reportIds = dr["Report ID"].ToString().Split(' ', '\n', '\t');
var conditionNo = dr["Test Condition ID"].ToString();
IEnumerable <DataTable> lastTables = new List <DataTable>();
lastTables = value.Where(e => e.TableName == "Reports - " + trans.HighLevelTxnDesc);
DataTable businessRuleTable = new DataTable();
businessRuleTable = lastTables.ElementAt(0);
DataRow[] dRow = businessRuleTable.Select("[Test_Cond_ID] = '" + conditionNo + "'");//fetching the row based on the test condition id
if (dRow.Length > 0)
{
foreach (DataRow dataRow in dRow)//looping the fetched row based on the test condition id
{
foreach (var reportid in reportIds)
{
if (dataRow["Report_ID"].ToString() != "" && reportid == dataRow["Report_ID"].ToString() && conditionNo.ToString() == item.ConditionNo)
{
strBuilder.Append(dataRow.Table.Columns["Report_ID"].ColumnName + ": " + dataRow["Report_ID"].ToString());
strBuilder.AppendLine();
ruleReferenceBuilder.Append(dataRow["Report_ID"].ToString());
ruleReferenceBuilder.AppendLine();
}
}
}
}
}
//Validations to be fetched based on the test condition id
if (dr["Business Rule ID"].ToString() != "" || dr["Interface ID"].ToString() != "" ||
dr["Channel Alert ID"].ToString() != "" || dr["Report ID"].ToString() != "")
{
//since we need noth column name and value as well
var validation1 = dr.Table.Columns["Validation 1"].ColumnName;
var validation1data = dr["Validation 1"].ToString();
var validation2 = dr.Table.Columns["Validation 2"].ColumnName;
var validation2data = dr["Validation 2"].ToString();
var validation3 = dr.Table.Columns["Validation 3"].ColumnName;
var validation3data = dr["Validation 3"].ToString();
var validation4 = dr.Table.Columns["Validation 4"].ColumnName;
var validation4data = dr["Validation 4"].ToString();
var validation5 = dr.Table.Columns["Validation 5"].ColumnName;
var validation5data = dr["Validation 5"].ToString();
//if value is empty, then do not write the column name and the value
if (validation1data != "")
{
strBuilder.Append(validation1 + ": " + validation1data);
strBuilder.AppendLine();
}
if (validation2data != "")
{
strBuilder.Append(validation2 + ": " + validation2data);
strBuilder.AppendLine();
}
if (validation3data != "")
{
strBuilder.Append(validation3 + ": " + validation3data);
strBuilder.AppendLine();
}
if (validation4data != "")
{
strBuilder.Append(validation4 + ": " + validation4data);
strBuilder.AppendLine();
}
if (validation5data != "")
{
strBuilder.Append(validation5 + ": " + validation5data);
strBuilder.AppendLine();
}
}
logicalDay = dr["Logical Day"].ToString();
batchFreq = dr["Batch Frequency"].ToString();
gapReference = dr["Gap Reference"].ToString();
for (int tranCol = 3; tranCol < dr.Table.Columns.IndexOf("Test Condition Result"); tranCol++)
{
strAttr.Append(dr.Table.Columns[tranCol].ColumnName + ": " + dr[tranCol].ToString());
strAttr.AppendLine();
}
break;
}
}
//Rule reference
colIndex++;
cell = ws.Cells[rowIndex, colIndex];
cell.Value = ruleReferenceBuilder.ToString();
ScenarioBuilder.FormatCell(cell);
cell.Style.WrapText = true;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
colIndex++;
//Scenario No.
string scenarioNo = item.ScenarioNo;
scenarioNo = scenarioNo.Replace("AT", "SL");
cell = ws.Cells[rowIndex, colIndex];
cell.Value = scenarioNo;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.AutoFitColumns();
colIndex++;
//Scenario Description
string scenarioDesc = GetScenarioDescription(testScenarios, lstHighLevelTransactions, testScenarioIndex);
cell = ws.Cells[rowIndex, colIndex];
cell.Value = scenarioDesc;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.WrapText = true;
colIndex++;
//Condition No
cell = ws.Cells[rowIndex, colIndex];
cell.Value = item.ConditionNo;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.AutoFitColumns();
colIndex++;
//Logical Day
cell = ws.Cells[rowIndex, colIndex];
cell.Value = logicalDay;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.AutoFitColumns();
colIndex++;
//Batch Frequency
cell = ws.Cells[rowIndex, colIndex];
cell.Value = batchFreq;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.AutoFitColumns();
colIndex++;
//Pre-Requisites
cell = ws.Cells[rowIndex, colIndex];
cell.Value = "";
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.AutoFitColumns();
colIndex++;
//Functionality
cell = ws.Cells[rowIndex, colIndex];
cell.Value = trans.HighLevelTxnDesc;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.AutoFitColumns();
colIndex++;
//Gap Reference
cell = ws.Cells[rowIndex, colIndex];
cell.Value = gapReference;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.AutoFitColumns();
colIndex++;
//Test Case No.
var condNo = item.ConditionNo.Trim().Substring(item.ConditionNo.Trim().LastIndexOf("TC"));
cell = ws.Cells[rowIndex, colIndex];
cell.Value = scenarioNo + "_" + condNo;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.AutoFitColumns();
colIndex++;
//Test Case Description.
cell = ws.Cells[rowIndex, colIndex];
cell.Value = strBuilder.ToString();
if (item.ConditionResult == "Negative")
{
cell.Style.Font.Color.SetColor(Color.Red);
}
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.WrapText = true;
colIndex++;
//Test Data
cell = ws.Cells[rowIndex, colIndex];
cell.Value = strAttr.ToString();
if (item.ConditionResult == "Negative")
{
cell.Style.Font.Color.SetColor(Color.Red);
}
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.WrapText = true;
colIndex++;
//Expected Result
cell = ws.Cells[rowIndex, colIndex];
cell.Value = trans.HighLevelTxnDesc + " should be created/generated successfully";
if (item.ConditionResult == "Negative")
{
cell.Style.Font.Color.SetColor(Color.Red);
}
ScenarioBuilder.FormatCell(cell);
cell.Style.ShrinkToFit = true;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.AutoFitColumns();
rowIndex++;
}
}
testScenarioIndex++;
}
}
catch (Exception)
{
throw;
}
}
