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;
}
}
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 ExportDAFile(int id)
{
try
{
ExportDesignAcceleratorViewModel ExportDAViewModel = new ExportDesignAcceleratorViewModel();
ProductsViewModel prodviewmodel = new ProductsViewModel();
//if (id == null)
// id = (int)TempData["daId"];
var lstprods = prodviewmodel.GetProducts(id);
var lstExportProducts = ExportDAViewModel.GetProducts(id);
var lstExportTransactions = ExportDAViewModel.GetTransactions(id);
IList <tbl_Attribute> lstExportAttributes = ExportDAViewModel.GetAttributes(id);
List <string> lstSheetNames = new List <string>();
lstSheetNames.Add("Products");
lstSheetNames.Add("Transactions");
lstSheetNames.Add("Transaction Attributes");
lstSheetNames.Add("Business Rules");
lstSheetNames.Add("Interfaces");
lstSheetNames.Add("Channels");
lstSheetNames.Add("Reports");
int i = 1;
string tbl = "";
using (ExcelPackage objExcelPackage = new ExcelPackage())
{
foreach (var sheet in lstSheetNames)
{
DataTable dt = new DataTable();
switch (sheet)
{
case "Products":
dt = CreateProductTable();
break;
case "Transactions":
dt = CreateTransactionsTable();
break;
case "Transaction Attributes":
dt = CreateAttributesTable(lstExportAttributes, id);
break;
case "Business Rules":
dt = CreateBusinessRulesTable(id, tbl);
break;
case "Interfaces":
dt = CreateInterfaceTable(id);
break;
case "Channels":
dt = CreateChannelsAlertsTable(id);
break;
case "Reports":
dt = CreateReportsTable(id);
break;
default:
break;
}
ExcelWorksheet ws = CreateSheet(objExcelPackage, sheet, dt, i);
if (sheet == "Transaction Attributes" || sheet == "Interfaces" || sheet == "Channels" || sheet == "Reports" || sheet == "Business Rules")
{
ws.Cells[2, 1].Value = sheet;
ws.Cells[2, 1, 2, 3].Merge = true; //Merge columns start and end range
ws.Cells[2, 1, 2, 3].Style.Font.Bold = true; //Font should be bold
ws.Cells[2, 1, 2, 3].Style.Font.Size = 20;
ws.Cells[2, 1, 2, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; // Alignment is center
ws.Cells[2, 1, 2, 3].Style.Fill.PatternType = ExcelFillStyle.Solid; // Alignment is center
ws.Cells[2, 1, 2, 3].Style.Fill.BackgroundColor.SetColor(Color.LightGray); // Alignment is center
var border = ws.Cells[2, 1, 2, 3].Style.Border;
border.Bottom.Style = ExcelBorderStyle.Medium;
border.Top.Style = ExcelBorderStyle.Thin;
border.Left.Style = ExcelBorderStyle.None;
border.Right.Style = ExcelBorderStyle.None;
int colIndex, rowIndex;
if (sheet == "Business Rules")
{
CreateTableHeaderForBR(dt, ws, out colIndex, out rowIndex, "brtbl", count);
}
else if (sheet == "Reports")
{
CreateTableHeaderForReports(dt, ws, out colIndex, out rowIndex, "Reports", count);
}
else if (sheet == "Transaction Attributes")
{
CreateTableHeaderForBR(dt, ws, out colIndex, out rowIndex, "txnAttr", count);
}
else if (sheet == "Interfaces")
{
CreateTableHeaderForBR(dt, ws, out colIndex, out rowIndex, "Interfaces", count);
}
else if (sheet == "Channels")
{
CreateTableHeaderForBR(dt, ws, out colIndex, out rowIndex, "Channels", count);
}
else
{
CreateTableHeaderForBR(dt, ws, out colIndex, out rowIndex, "", count);
}
AddRowsHLT(dt, ws, ref colIndex, ref rowIndex, count, id);
ws.View.ShowGridLines = false;
ws.View.ZoomScale = 80;
ws.Cells.AutoFitColumns();
int j;
if (sheet == "Business Rules")
{
for (j = 5; j <= count + 4; j++)
{
ws.Column(j).Width = 3.57;
}
}
else if (sheet == "Reports")
{
for (j = 6; j <= count + 5; j++)
{
ws.Column(j).Width = 3.57;
}
}
else if (sheet == "Transaction Attributes")
{
for (j = 4; j <= count + 3; j++)
{
ws.Column(j).Width = 3.57;
}
}
else if (sheet == "Interfaces")
{
for (j = 8; j <= count + 7; j++)
{
ws.Column(j).Width = 3.57;
}
}
else if (sheet == "Channels")
{
for (j = 5; j <= count + 4; j++)
{
ws.Column(j).Width = 3.57;
}
}
if (sheet == "Business Rules")
{
ws.DeleteColumn(3);
}
else if (sheet == "Interfaces")
{
ws.DeleteColumn(4);
}
else if (sheet == "Channels")
{
var cnt = ws.Dimension.End.Column;
ws.DeleteColumn(cnt);
}
else if (sheet == "Transaction Attributes")
{
ws.DeleteColumn(count + 6);
}
else if (sheet == "Reports")
{
ws.DeleteColumn(4);
var cnt = ws.Dimension.End.Column;
ws.DeleteColumn(cnt);
}
i++;
}
else
{
ws.Cells[2, 1].Value = sheet;
ws.Cells[2, 1, 2, 3].Merge = true; //Merge columns start and end range
ws.Cells[2, 1, 2, 3].Style.Font.Bold = true; //Font should be bold
ws.Cells[2, 1, 2, 3].Style.Font.Size = 20;
ws.Cells[2, 1, 2, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; // Alignment is center
ws.Cells[2, 1, 2, 3].Style.Fill.PatternType = ExcelFillStyle.Solid; // Alignment is center
ws.Cells[2, 1, 2, 3].Style.Fill.BackgroundColor.SetColor(Color.LightGray); // Alignment is center
var border = ws.Cells[2, 1, 2, 3].Style.Border;
border.Bottom.Style = ExcelBorderStyle.Medium;
border.Top.Style = ExcelBorderStyle.Thin;
border.Left.Style = ExcelBorderStyle.None;
border.Right.Style = ExcelBorderStyle.None;
int colIndex, rowIndex;
CreateTableHeader(dt, ws, out colIndex, out rowIndex, tbl);
AddRows(dt, ws, ref colIndex, ref rowIndex);
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(id);
ExcelCommonFunctions excelCommonfunctions = new ExcelCommonFunctions();
string filePath = excelCommonfunctions.SaveFile(objExcelPackage, da.daName, "", "ExportDA");
return(filePath);
}
}
catch (Exception)
{
throw;
}
}
