// GET: TestDesign
public async Task <string> GenerateTestDesign(int daId, HttpPostedFileBase txmPath, HttpPostedFileBase ScbPath)
{
try
{
Dictionary <string, List <TestScenarioMembers> > testScenarios = new Dictionary <string, List <TestScenarioMembers> >();
IList <tbl_Attribute> lstAttributes = new List <tbl_Attribute>();
TransactionMatrix transactionMatrix = new TransactionMatrix();
testDesignVM.lstHighLevelTransactions = testDesignVM.GetTransactionsList(daId).lstTransactions;
ModuleViewModel moduleViewModel = new ModuleViewModel();
int colIndex = 1, rowIndex = 0;
//Create new workbook for Test Design
using (var package = new ExcelPackage())
{
ExcelWorksheet ws2 = package.Workbook.Worksheets.Add("Design Document");
CreateHeadersForTD(ws2, out dtTD);
//Read data from Test Scenario
using (var objExcelPackage = new ExcelPackage(ScbPath.InputStream))
{
//string scenarioId = "";
//string testConditionId = "";
ExcelWorksheet ws = excelCommonFunctions.OpenSheet(objExcelPackage, "Test Scenarios");
testScenarios = GetDataTableFromExcel(ScbPath);
}
//Read data from RuleOfNData
DataSet transactionMatrixSheets = new DataSet();
//list of the rule reference datatables will come into this list
List <DataTable> dtRuleOfNforTestDesignRuleReference = new List <DataTable>();
//Dictionary to add the fetched last tables from tm into dtRuleOFNForTestDesignRuleReference and pass as a parameter to addTestDesign method.
Dictionary <string, List <DataTable> > ruleReferences = new Dictionary <string, List <DataTable> >();
using (var objExcelPackage = new ExcelPackage(txmPath.InputStream))
{
foreach (var trans in testDesignVM.lstHighLevelTransactions)
{
int colIdex = 1, rowIdex = 0;
ExcelWorksheet ws = excelCommonFunctions.OpenSheet(objExcelPackage, trans.HighLevelTxnDesc);
//For Test design last tables
dtRuleOfNforTestDesignRuleReference = transactionMatrix.GetRuleOfNDataForTestDesignRuleReferene(ws, ref colIdex, ref rowIdex);
//Adding all the fetched tables into the dictionary from list
foreach (var item in dtRuleOfNforTestDesignRuleReference)
{
if (ruleReferences.ContainsKey(ws.ToString()))
{
ruleReferences[ws.ToString()].Add(item);
}
else
{
ruleReferences.Add(ws.ToString(), new List <DataTable> {
item
});
}
}
DataTable dtRuleOfN = transactionMatrix.GetRuleOfNData(ws, ref colIdex, ref rowIdex);
dtRuleOfN.TableName = trans.HighLevelTxnDesc;
transactionMatrixSheets.Tables.Add(dtRuleOfN);
}
}
tbl_DesignAccelerator da = new tbl_DesignAccelerator();
DAManager daManager = new DAManager();
da = daManager.FindDA(daId);
var modId = da.ModuleId;
tbl_Module daModule = new tbl_Module();
ModuleManager modManager = new ModuleManager();
daModule = modManager.FindModuleNameForRunPlan(modId);
var moduleName = daModule.ModuleName;
string tblName4 = moduleName + " - Test Design Document";
ws2.Cells[2, 1].Value = tblName4;
//// Format Excel Sheet
ws2.Cells[2, 1, 2, 5].Merge = true; //Merge columns start and end range
ws2.Cells[2, 1, 2, 5].Style.Font.Bold = true; //Font should be bold
ws2.Cells[2, 1, 2, 5].Style.Font.Size = 14;
ws2.Cells[2, 1, 2, 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; // Alignment is Left
ws2.Cells[2, 1, 2, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; // Border
ws2.Cells[2, 1, 2, 5].Style.Fill.BackgroundColor.SetColor(Color.LightGray); // Background Color
excelCommonFunctions.CreateTableHeader(dtTD, ws2, ref colIndex, ref rowIndex, "tbl4");
ws2.Cells["A4:N4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
AddTestDesign(testScenarios, transactionMatrixSheets, ws2, colIndex, rowIndex, dtTD, testDesignVM.lstHighLevelTransactions, ruleReferences);
//Format Excel Sheet
ws2.View.ShowGridLines = false;
ws2.View.ZoomScale = 80;
ws2.Cells.AutoFitColumns();
ws2.Column(4).Width = 50;
ws2.Column(12).Width = 75;
ws2.Column(13).Width = 75;
ws2.Column(14).Width = 75;
string filePath = excelCommonFunctions.SaveFile(package, da.daName, "", "TD");
return(filePath);
}
}
catch (Exception)
{
throw;
}
}
public void AddRunPlanData(DataTable testDesignTableData, int daId, string moduleName)
{
try
{
int colIndex = 1;
int rowIndex = 11;
string scenarioNo = string.Empty;
string functionality = string.Empty;
string testCaseDesc = string.Empty;
string preReqs = string.Empty;
string testCaseId = string.Empty;
string testScenarioId = string.Empty;
string module = string.Empty;
string status = string.Empty;
string defectId = string.Empty;
string remarks = string.Empty;
string conditionNo = string.Empty;
int counter = 1;
int noRunCount = 1;
int passCount = 0;
int failCount = 0;
int blockedCount = 0;
int incompleteCount = 0;
int notApplicableCount = 0;
ExcelWorksheet runPlanws = null;
Dictionary <string, List <DataRow> > totalDays = new Dictionary <string, List <DataRow> >();
int i = 0;
for (; i < testDesignTableData.Rows.Count; i++)
{
var logicalDay = testDesignTableData.Rows[i]["Logical Day"].ToString();
logicalDay = Regex.Replace(logicalDay, @"\t|\n|\r", "");
logicalDay = logicalDay.Trim();
var checkExistingWs = package.Workbook.Worksheets.FirstOrDefault(w => w.Name == logicalDay.ToString());
// package.Workbook.Worksheets.OrderBy(package.Workbook.Worksheets);
if (checkExistingWs == null)
{
runPlanws = package.Workbook.Worksheets.Add(logicalDay.ToString());
CreateHeadersForRunPlan(runPlanws, out dtRPstatus);
CreateHeadersForRunPlanData(runPlanws, out dtRPData);
string displayName = "Run Plan";
runPlanws.Cells[2, 1].Value = displayName;
var cellFormat = runPlanws.Cells[2, 1, 2, 4];
cellFormat.Merge = true;
cellFormat.Style.Font.Bold = true;
cellFormat.Style.Font.Size = 20;
cellFormat.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cellFormat.Style.Fill.PatternType = ExcelFillStyle.Solid;
cellFormat.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
excelCommonFunctions.CreateTableHeader(dtRPstatus, runPlanws, ref colIndex, ref rowIndex, "RunPlanStatus");
excelCommonFunctions.CreateTableHeader(dtRPData, runPlanws, ref colIndex, ref rowIndex, "RunPlanData");
colIndex = 1;
rowIndex++;
runPlanws.View.ShowGridLines = false;
runPlanws.View.ZoomScale = 80;
runPlanws.Cells.AutoFitColumns();
runPlanws.Column(1).Width = 20;
runPlanws.Column(2).Width = 30;
runPlanws.Column(3).Width = 20;
runPlanws.Column(4).Width = 20;
runPlanws.Column(5).Width = 20;
runPlanws.Column(6).Width = 20;
runPlanws.Column(7).Width = 55;
runPlanws.Column(8).Width = 17;
runPlanws.Column(9).Width = 18;
runPlanws.Column(10).Width = 15;
DataRow[] dRow = testDesignTableData.Select("[Logical Day]= '" + logicalDay + "'");
var count = dRow.Length;
if (dRow.Length > 0)
{
foreach (var dr in dRow)
{
scenarioNo = dr["Scenario No."].ToString();
functionality = dr["Functionality"].ToString();
testCaseDesc = dr["Test Case Description"].ToString();
preReqs = dr["Pre-Requisites"].ToString();
conditionNo = dr["Condition No."].ToString();
if (totalDays.ContainsKey(logicalDay.ToString()))
{
totalDays[logicalDay.ToString()].Add(dr);
//for main data table
//count i.e serial No.
var cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = counter;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.WrapText = true;
colIndex++;
//Module name
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = moduleName;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.WrapText = true;
colIndex++;
//functionality
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = functionality;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.WrapText = true;
colIndex++;
//Test Scenario Id.
string scnId = scenarioNo.Replace("SL", "RD");
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = scnId;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.WrapText = true;
colIndex++;
//Prerequisites
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = preReqs;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.WrapText = true;
colIndex++;
//Test Case Id
string testcaseId = conditionNo.Substring(conditionNo.Length - 6);
cell = runPlanws.Cells[rowIndex, colIndex];
if (scnId == "")
{
cell.Value = "";
}
else
{
cell.Value = scnId + "_" + testcaseId;
}
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.WrapText = true;
colIndex++;
//Test Case Description.
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = testCaseDesc;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.WrapText = true;
colIndex++;
//Status
var formulaCell = runPlanws.Cells[rowIndex, colIndex];
formulaCell.Value = "No Run";
var selectStatus = runPlanws.DataValidations.AddListValidation(formulaCell.ToString());
selectStatus.ShowErrorMessage = true;
selectStatus.ErrorStyle = OfficeOpenXml.DataValidation.ExcelDataValidationWarningStyle.stop;
selectStatus.ErrorTitle = "Error";
selectStatus.Error = "Please select a value present in the dropdown list";
selectStatus.Formula.Values.Add("Pass");
selectStatus.Formula.Values.Add("Fail");
selectStatus.Formula.Values.Add("No Run");
selectStatus.Formula.Values.Add("Blocked");
selectStatus.Formula.Values.Add("Not Completed");
selectStatus.Formula.Values.Add("Not Applicable");
ScenarioBuilder.FormatCell(formulaCell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.WrapText = true;
colIndex++;
//Defect Id
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = "";
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.WrapText = true;
colIndex++;
//Remarks
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = "";
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
cell.Style.WrapText = true;
//for status table
int statusRowno = 5;
int statusColno = 1;
//module name
var statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = moduleName;
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//total cases(planned cases)
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = count;
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//pass
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = passCount;
statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Pass""" + ")";
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//fail
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = failCount;
statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Fail""" + ")";
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//Blocked
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = blockedCount;
statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Blocked""" + ")";
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//not completed
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = 0;
statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Not Completed""" + ")";
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//No run
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = count;
statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""No Run""" + ")";
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//not applicable
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = notApplicableCount;
statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Not Applicable""" + ")";
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//Total executed cases
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = count;
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
rowIndex++;
colIndex = 1;
}
else
{
totalDays.Add(logicalDay.ToString(), new List <DataRow> {
dr
});
//for main data table
//count i.e serial No.
var cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = counter;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
colIndex++;
//Module name
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = moduleName;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
colIndex++;
//functionality
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = functionality;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
colIndex++;
//Test Scenario Id.
string scnId = scenarioNo.Replace("SL", "RD");
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = scnId;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
colIndex++;
//Prerequisites
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = preReqs;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
colIndex++;
//Test Case Id
string testcaseId = conditionNo.Substring(conditionNo.Length - 6);
cell = runPlanws.Cells[rowIndex, colIndex];
// since test case id depends on the scenario id, so if scenarioid is blank then test case id too will be blank.
if (scnId == "")
{
cell.Value = "";
}
else
{
cell.Value = scnId + "_" + testcaseId;
}
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
colIndex++;
//Test Case Description.
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = testCaseDesc;
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
colIndex++;
//Status
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = "No Run";
var selectStatus = runPlanws.DataValidations.AddListValidation(cell.ToString());
selectStatus.Formula.Values.Add("Pass");
selectStatus.Formula.Values.Add("Fail");
selectStatus.Formula.Values.Add("No Run");
selectStatus.Formula.Values.Add("Blocked");
selectStatus.Formula.Values.Add("Not Completed");
selectStatus.Formula.Values.Add("Not Applicable");
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
colIndex++;
//Defect Id
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = "";
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
colIndex++;
//Remarks
cell = runPlanws.Cells[rowIndex, colIndex];
cell.Value = "";
ScenarioBuilder.FormatCell(cell);
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//for status table
int statusRowno = 5;
int statusColno = 1;
//module name
var statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = moduleName;
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//total cases(planned cases)
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = count;
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//pass
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = passCount;
statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Pass""" + ")";
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//fail
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = failCount;
statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Fail""" + ")";
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//Blocked
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = blockedCount;
statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Blocked""" + ")";
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//Not completed
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = 0;
statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Not completed""" + ")";
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//No run
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = count;
statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""No Run""" + ")";
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//Not applicable
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = notApplicableCount;
statusCell.Formula = "COUNTIF(" + rowIndex + ":" + colIndex + "," + @"""Not Applicable""" + ")";
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//Total executed cases
statusCell = runPlanws.Cells[statusRowno, statusColno++];
statusCell.Value = count;
ScenarioBuilder.FormatCell(statusCell);
statusCell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
statusCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
rowIndex++;
colIndex = 1;
}
//Run No.
counter++;
}
}
//resetting the counter for another new sheet
counter = 1;
//resetting the count for another sheet
noRunCount = 1;
failCount = 1;
passCount = 1;
incompleteCount = 1;
blockedCount = 1;
notApplicableCount = 1;
}
}
//sorting sheets in order using linq lambda
var a = package.Workbook.Worksheets.OrderBy(w => PadNumbers(w.ToString()));//using this PadNumbers method to sort out the sheets in order,it is assigning in order although.
//looping and arranging the sorted sheets back.
foreach (var item1 in a)
{
package.Workbook.Worksheets.MoveToEnd(item1.Index);
}
}
catch (Exception)
{
throw;
}
}
public async Task <string> GenerateTransactionMatrix(int daId)
{
try
{
IList <sp_GetMappingViewModelData_Result> lstMappingViewModel = new List <sp_GetMappingViewModelData_Result>();
TransactionsManager transactions = new TransactionsManager();
MappingViewModel mappingViewModel = new MappingViewModel();
lstNegativeAttributeValues = mappingViewModel.GetNegativeAttributeValues(daId);
lstMappingViewModel = mappingViewModel.GetMappedData(daId);
var highLevelTransactions = transactions.GetAllTransactions(daId);
ExcelCommonFunctions excelCommonFunctions = new ExcelCommonFunctions();
IList <Required> rList = ReturnRequired(lstMappingViewModel, mappingViewModel, highLevelTransactions);
string filePath = "";
using (ExcelPackage objExcelPackage = new ExcelPackage())
{ // Format Excel Sheet
int i = 0;
foreach (var item in highLevelTransactions)
{
var dtMappingTable = from a in rList
where a.dtMappingTable.TableName == item.HighLevelTxnDesc
select a;
var dtTM = from a in rList
where a.dtTM.TableName == item.HighLevelTxnDesc
select a;
ExcelWorksheet ws = excelCommonFunctions.CreateSheet(objExcelPackage, item.HighLevelTxnDesc, i);
ws.Cells[1, 1].Value = "Transaction - " + item.HighLevelTxnDesc;
// Format Excel Sheet
ws.Cells[1, 1, 1, 7].Merge = true; //Merge columns start and end range
ws.Cells[1, 1, 1, 7].Style.Font.Bold = true; //Font should be bold
ws.Cells[1, 1, 1, 7].Style.Font.Size = 20;
ws.Cells[1, 1, 1, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; // Alignment is center
ws.Cells[1, 1, 1, 7].Style.Fill.PatternType = ExcelFillStyle.Solid; // Border
ws.Cells[1, 1, 1, 7].Style.Fill.BackgroundColor.SetColor(Color.LightGray); // Background Color
int colIndex = 1, rowIndex = 0;
excelCommonFunctions.CreateTableHeader(dtMappingTable.First().dtMappingTable, ws, ref colIndex, ref rowIndex, "tbl1");
excelCommonFunctions.AddRows(dtMappingTable.First().dtMappingTable, ws, ref colIndex, ref rowIndex);
excelCommonFunctions.CreateTableHeader(dtTM.First().dtTM, ws, ref colIndex, ref rowIndex, "tbl2");
excelCommonFunctions.AddRows(dtTM.First().dtTM, ws, ref colIndex, ref rowIndex);
//Format Excel Sheet
ws.View.ShowGridLines = false;
ws.View.ZoomScale = 80;
ws.Cells.AutoFitColumns();
i++;
}
tbl_DesignAccelerator da = new tbl_DesignAccelerator();
DAManager daManager = new DAManager();
da = daManager.FindDA(daId);
filePath = excelCommonFunctions.SaveFile(objExcelPackage, da.daName, "", "Rule of N - Txn Matrix");
}
return(filePath);
}
catch (Exception)
{
throw;
}
}
public string GenerateBusinessRuleMappingTable(int daId, HttpPostedFileBase path)
{
try
{
ExcelCommonFunctions excelCommonFunctions = new ExcelCommonFunctions();
TransactionMatrix transactionMatrix = new TransactionMatrix();
InterfaceMappingViewModel interfaceMappingView = new InterfaceMappingViewModel();
interfaceMappingView.lstHighLevelTxns = interfaceMappingView.GetTransactionsList(daId).lstTransactions;
BusinessRuleMappingViewModel buzRulesMappingView = new BusinessRuleMappingViewModel();
buzRulesMappingView.lstBuzRulesData = buzRulesMappingView.GetBuzRulesList(daId);
using (ExcelPackage objExcelPackage = new ExcelPackage(path.InputStream))
{
int cnt = 1;
foreach (var trans in interfaceMappingView.lstHighLevelTxns)
{
int colIndex = 1, rowIndex = 0;
int rowCountRuleofN;
ExcelWorksheet ws = excelCommonFunctions.OpenSheet(objExcelPackage, trans.HighLevelTxnDesc);
DataTable dtRuleOfN = transactionMatrix.GetRuleOfNDataForAllTables(ws, ref colIndex, ref rowIndex, "Business Rules - " + trans.HighLevelTxnDesc);
//get merged cells to find the end row of Rule of N table
if (ws.MergedCells.Count == 1)
{
rowCountRuleofN = dtRuleOfN.Rows.Count;
}
else
{
//the header row of Rule of N is fixed as 17
var c = ws.MergedCells[1]; //Assuming the first merged cell is of one mapping table.
ExcelAddress cellAddr = new ExcelAddress(c);
int row = cellAddr.Start.Row; //to get the row number of the merged cell
int lastRow = row - 2; //to get last row of Rule of N table
rowCountRuleofN = lastRow - 17; // to get count of rows of RuleofN table excluding header.
}
//create datatable for each transaction
DataTable dtBuzRules = BusinessRuleMappingViewModel.CreateBuzRulesDataTable(buzRulesMappingView.lstBuzRulesData, trans.TransactionSeq, dtRuleOfN);
//Table#2
if (dtBuzRules.Rows.Count != 0)
{
rowIndex = rowIndex + 2;
string tblName4 = "Business Rules - " + trans.HighLevelTxnDesc;
ws.Cells[rowIndex, 1].Value = tblName4;
// ws.Cells[rowIndex, 1, rowIndex, 3].Merge = true; //Merge columns start and end range
ws.Cells[rowIndex, 1, rowIndex, 3].Style.Font.Bold = true; //Font should be bold
ws.Cells[rowIndex, 1, rowIndex, 3].Style.Font.Size = 20;
ws.Cells[rowIndex, 1, rowIndex, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; // Alignment is center
ws.Cells[rowIndex, 1, rowIndex, 3].Style.Fill.PatternType = ExcelFillStyle.Solid; // Aligmnet is center
ws.Cells[rowIndex, 1, rowIndex, 3].Style.Fill.BackgroundColor.SetColor(Color.LightGray); // Alignment is center
colIndex = 1;
rowIndex = rowIndex + 2;
excelCommonFunctions.CreateTableHeader(dtBuzRules, ws, ref colIndex, ref rowIndex, tblName4);
Dictionary <string, string> dictBuzRules = excelCommonFunctions.GetMappingID(dtBuzRules);
excelCommonFunctions.AddRowsMapping(dtBuzRules, ws, dictBuzRules, rowCountRuleofN, ref colIndex, ref rowIndex);
}
ws.View.ShowGridLines = false;
ws.View.ZoomScale = 80;
//ws.Cells.AutoFitColumns();
//rearranged
cnt++;
}
tbl_DesignAccelerator da = new tbl_DesignAccelerator();
DAManager daManager = new DAManager();
da = daManager.FindDA(daId);
string filePath = excelCommonFunctions.SaveFile(objExcelPackage, da.daName, path.FileName, "BR");
return(filePath);
}
}
catch (Exception)
{
throw;
}
}
