/// <summary>
/// Iterating through all the components for slicers, pivot, row fields, and value columns and collecting them in Hash Tables
/// Passing them to other method wherein all the pivot tables, slicers, and row fields, filters are created based on values from
/// their hashTables.
/// </summary>
/// <param name="InputCSVFolderPath"></param>
/// <param name="component"></param>
/// <param name="PivotOutputReportFullPath"></param>
/// <param name="dSummaryViewComponents"></param>
/// <param name="htHyperLinks"></param>
/// <param name="otherNodes"></param>
private static void GeneratePivotReportForMultipleFiles(
string InputCSVFolderPath,
XmlNode component, string PivotOutputReportFullPath,
ref Dictionary <string, string[]> dSummaryViewComponents,
ref Hashtable htHyperLinks,
List <XmlNode> otherNodes)
{
StringBuilder exceptionCommentsInfo = new StringBuilder();
int numberOfFilesCount = 1;
string sheetName = string.Empty;
try
{
//Pivot Report - RowFields
Hashtable htRowPivotfields;
//Pivot Report - PageFilters
Hashtable htPivotPageFilters;
//Pivot Report - PageSlicers
Hashtable htPageSlicers;
//Slicers and Dicers HashTables
Hashtable htComponentSliceandDiceViews = new Hashtable();
int RowPivotfieldCount = 1, PivotPageFiltersCount = 1;
int sliceandDiceViewsCount = 1;
//Get the Components Lists by Reading the Component Tags and Attribute.
//Example Content Types, Master Pages, etc from Pivot Config XML
string componentName = component.Attributes["Name"].InnerText;
string InputFileName = component.Attributes["InputFileName"].InnerText;
string summaryViewColumn = component.Attributes["SummaryViewColumn"].InnerText;
string description = component.SelectSingleNode("Description").InnerText;
string componentSliceAndDiceSheetname = "";
//Exception Comments
exceptionCommentsInfo.Append("ComponentName: " + componentName + ", InputFileName: " + InputFileName + ", SummaryViewColumn: " + summaryViewColumn);
//Read All The INput File CSV for a Component. If Any Components has the multiple Usage or Input File, we are reading all files
//Example: If Content Type Usage Files are - ContentType_Usage.csv, ContentType_Usage_03112016_035049.csv
string searchpattern = System.IO.Path.GetFileNameWithoutExtension(InputCSVFolderPath + "\\" + InputFileName);
searchpattern = searchpattern + "*.csv";
string[] files = FileUtility.FindAllFilewithSearchPattern(InputCSVFolderPath, searchpattern);
//Null Check
if (files != null)
{
if (files.Count() > 0)
{
foreach (string filePath in files)
{
string inputCSVFile = filePath;
string inputFileName = filePath.Substring(filePath.LastIndexOf("\\") + 1);
htRowPivotfields = new Hashtable();
htPivotPageFilters = new Hashtable();
htPageSlicers = new Hashtable();
if (System.IO.File.Exists(inputCSVFile))
{
//Get Data InputDataSheetName
string inputDataSheetName = System.IO.Path.GetFileNameWithoutExtension(filePath);
//Deleting columns from csv
if (inputDataSheetName == "CThavingFeatureIDTag_Definition_Usage" || inputDataSheetName == "ListTemplates_Usage" || inputDataSheetName == "PreMT_CThavingFeatureIDTag_Definition")
{
deleteColumnFromCSV(inputDataSheetName, InputCSVFolderPath, inputCSVFile, inputFileName);
}
exceptionCommentsInfo.Clear();
exceptionCommentsInfo.Append(", InputDataSheetName: " + inputDataSheetName);
Excel.Application oApp;
Excel.Worksheet oSheet;
Excel.Workbook oBook = null;
oApp = new Excel.Application();
try
{
oBook = oApp.Workbooks.Open(inputCSVFile);
if (inputDataSheetName.Length >= Constants.SheetNameMaxLength)
{
oSheet = (Excel.Worksheet)oBook.Sheets.get_Item(1);
}
else
{
oSheet = (Excel.Worksheet)oBook.Sheets.get_Item(inputDataSheetName);
}
// Now capture range of the first sheet
Excel.Range oRange = oSheet.UsedRange;
if (oRange.Rows.Count > 1)
{
if (files.Count() > 1)
{
sheetName = componentName + "_" + numberOfFilesCount;
//SliceAndDiceSheet
componentSliceAndDiceSheetname = componentName + "_" + numberOfFilesCount + Constants.SliceAndDiceSheet_Suffix;
}
else
{
sheetName = componentName;
componentSliceAndDiceSheetname = componentName + Constants.SliceAndDiceSheet_Suffix;
}
dSummaryViewComponents.Add(sheetName, new string[] { inputCSVFile, summaryViewColumn });
string pivotCountField = string.Empty; string pivotSliceandDiceCountField = string.Empty;
string slicerStyle = string.Empty;
//Read Item Tags inside the Component Tag, to generate - Pivot and Slice and Dice View
foreach (XmlNode ItemNode in component.SelectNodes("Item"))
{
string itemType = ItemNode.Attributes["Type"].InnerText;
//Create Pivot View
if (itemType == "PivotView")
{
var count = ItemNode.SelectSingleNode("ValueColumn");
pivotCountField = count.Attributes["Name"].InnerText;
var slicerstyle = ItemNode.SelectSingleNode("SlicersStyling");
slicerStyle = slicerstyle.Attributes["Style"].InnerText;
//Row Filter
foreach (XmlNode RowFieldRoot in ItemNode.SelectNodes("Rows"))
{
foreach (XmlNode rowFeild in RowFieldRoot.ChildNodes)
{
string rowPageFieldName = rowFeild.Attributes["Column"].InnerText;
string rowPageLabel = rowFeild.Attributes["Label"].InnerText;
htRowPivotfields.Add(RowPivotfieldCount, rowPageFieldName + "~" + rowPageLabel);
RowPivotfieldCount++;
}
}
//Page Filters
foreach (XmlNode FilterFeildRoot in ItemNode.SelectNodes("Filters"))
{
foreach (XmlNode filterFeild in FilterFeildRoot.ChildNodes)
{
string filterName = filterFeild.InnerText;
htPivotPageFilters.Add(PivotPageFiltersCount, filterName);
PivotPageFiltersCount++;
}
}
//Slicers
foreach (XmlNode SlicersRoot in ItemNode.SelectNodes("Slicers"))
{
int i = 0;
i = SlicersRoot.ChildNodes.Count - 1;
foreach (XmlNode SlicersRootFeild in SlicersRoot.ChildNodes)
{
string slicerName = SlicersRootFeild.InnerText;
htPageSlicers.Add(i, slicerName + "~" + slicerStyle);
i--;
}
}
}
//Create Slice and Dice View Sheet
if (itemType == "SliceDiceView")
{
//Loop views for each Component
foreach (XmlNode viewsNode in ItemNode.SelectNodes("Views"))
{
foreach (XmlNode viewNode in viewsNode.ChildNodes)
{
htComponentSliceandDiceViews.Add(inputDataSheetName + sliceandDiceViewsCount, viewNode.OuterXml);
sliceandDiceViewsCount++;
}
}
}
}
//Pivot Sheet
string componentPivotSheetName = sheetName;
//Length of Sheet Name Should be less than 31 Char
if (componentPivotSheetName.Length >= Constants.SheetNameMaxLength)
{
componentPivotSheetName = componentPivotSheetName.Substring(0, Constants.SheetNameMaxLength);
}
//Length of Sheet Name Should be less than 31 Char
if (componentSliceAndDiceSheetname.Length >= Constants.SheetNameMaxLength)
{
componentSliceAndDiceSheetname = componentSliceAndDiceSheetname.Substring(0, Constants.SheetNameMaxLength);
}
//Create Pivot View and Slicer View Sheet
PivotViewHelper.GeneratePivotAndSlicersView(inputCSVFile, PivotOutputReportFullPath, ref componentPivotSheetName, inputDataSheetName, componentPivotSheetName, htRowPivotfields, htPivotPageFilters, htPageSlicers, pivotCountField, component, numberOfFilesCount, otherNodes);
htHyperLinks.Add(sheetName + "~" + inputFileName, description);
RowPivotfieldCount = 1;
PivotPageFiltersCount = 1;
htPageSlicers = null;
htPivotPageFilters = null;
htRowPivotfields = null;
sliceandDiceViewsCount = 1;
numberOfFilesCount += 1;
}
else
{
Logger.LogInfoMessage(string.Format("[GeneratePivotReports][GeneratePivotReportForMultipleFiles]" + " No records available for the component " + componentName + " in file " + inputDataSheetName), true);
}
object misValue = System.Reflection.Missing.Value;
oBook.Close(false, misValue, misValue);
oApp.Application.Quit();
oApp.Quit();
Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oBook);
}
catch (Exception ex)
{
if (oBook != null)
{
oBook.Close();
}
if (oApp != null)
{
oApp.Quit();
oApp.Application.Quit();
}
Marshal.ReleaseComObject(oBook);
Logger.LogErrorMessage(string.Format("[GeneratePivotReports][GeneratePivotReportForMultipleFiles][Exception]: " + ex.Message), true);
ExceptionCsv.WriteException(Constants.NotApplicable, Constants.NotApplicable, Constants.NotApplicable, "Pivot", ex.Message, ex.ToString(),
"[GeneratePivotReports]: GeneratePivotReportForMultipleFiles", ex.GetType().ToString(), "ExceptionCommentsInfo: " + exceptionCommentsInfo);
}
finally
{
Marshal.ReleaseComObject(oApp);
oApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
if (inputDataSheetName == "CThavingFeatureIDTag_Definition_Usage" || inputDataSheetName == "ListTemplates_Usage" || inputDataSheetName == "PreMT_CThavingFeatureIDTag_Definition")
{
string path1 = InputCSVFolderPath + @"\" + "Backup";
//copy file to backup folder
string destCSVFile1 = path1 + @"\" + inputFileName;
System.IO.File.Copy(destCSVFile1, inputCSVFile, true);
DeleteFolderAndFiles(path1);
}
}
else
{
string ErrorMessage = "[GeneratePivotReports][GeneratePivotReportForMultipleFiles] File Not Found Error: Input CSV file is not present in path:" + inputCSVFile + ", ExceptionCommentsInfo: " + exceptionCommentsInfo;
Logger.LogErrorMessage(ErrorMessage, true);
ExceptionCsv.WriteException(Constants.NotApplicable, Constants.NotApplicable, Constants.NotApplicable, "Pivot", "File Not Found", ErrorMessage,
"[GeneratePivotReports]: GeneratePivotReportForMultipleFiles", "File Not Found", "Input CSV File (" + inputCSVFile + ")");
}
}
}
else
{
string ErrorMessage = "[GeneratePivotReports][GeneratePivotReportForMultipleFiles] File Not Found Error: Input CSV file is not present in path:" + InputCSVFolderPath + "\\" + InputFileName + ", " + exceptionCommentsInfo;
Logger.LogErrorMessage(ErrorMessage, true);
ExceptionCsv.WriteException(Constants.NotApplicable, Constants.NotApplicable, Constants.NotApplicable, "Pivot", "File Not Found", ErrorMessage,
"[GeneratePivotReports]: GeneratePivotReportForMultipleFiles", "File Not Found", "Input CSV File (" + InputCSVFolderPath + "\\" + InputFileName + ")");
}
}
else
{
string ErrorMessage = "[GeneratePivotReports][GeneratePivotReportForMultipleFiles] File Not Found Error: Input CSV file is not present in path InputCSVFolderPath: " + InputCSVFolderPath + ", SearchPattern: " + searchpattern + ", " + exceptionCommentsInfo;
Logger.LogErrorMessage(ErrorMessage, true);
}
}
catch (Exception ex)
{
Logger.LogErrorMessage(string.Format("[GeneratePivotReports][GeneratePivotReportForMultipleFiles][Exception]: " + ex.Message + ", ExceptionCommentsInfo: " + exceptionCommentsInfo), true);
ExceptionCsv.WriteException(Constants.NotApplicable, Constants.NotApplicable, Constants.NotApplicable, "Pivot", ex.Message, ex.ToString(),
"[GeneratePivotReports]: GeneratePivotReportForMultipleFiles", ex.GetType().ToString(), exceptionCommentsInfo.ToString());
}
}
