private void InitializeProcessedDataStorage()
{
KPIReportData.CurrentMonthKPIDatas = new DataSet();
var kpiSheetConfigs = reportConfig.GetAllKPISheetConfigs()
.Where(c => c.Independency);
foreach (var sheetConfig in kpiSheetConfigs)
{
if (sheetConfig != null && sheetConfig.DataColumns.Count > 0)
{
DataTable kpiTable = new DataTable(sheetConfig.TableName);
kpiTable.FillSchemaByColumns(sheetConfig.DataColumns);
KPIReportData.CurrentMonthKPIDatas.Tables.Add(kpiTable);
}
}
}
public void Read()
{
#region Read Raw Data
KPIReportData.RawDatas = new DataSet();
KPIReportData.RawTitleDatas = new DataSet();
Action<ExcelHelper, IEnumerable<RawSheetConfig>> ReadRawSheetDatas = ((e, sheetConfigs) =>
{
foreach (var sheetConfig in sheetConfigs)
{
FinaChanLogger.Info("Load raw data of {0} worksheet start", sheetConfig.Name);
DataTable rawDataTable = new DataTable(sheetConfig.TableName);
//FinaChanCore.FillTableSchemaByColumns(sheetConfig.DataColumns, rawDataTable);
rawDataTable.FillSchemaByColumns(sheetConfig.DataColumns);
DataTable rawFormulaTable = null;
var containFormulaColumns = sheetConfig.DataColumns.Where(c => c.ContainFormula);
if (containFormulaColumns.Count() > 0)
{
rawFormulaTable = new DataTable(string.Concat(sheetConfig.TableName, "Formulas"));
foreach (var column in containFormulaColumns)
{
rawFormulaTable.Columns.Add(column.Name);
}
}
DataTable rawTitleDataTable = null;
if (sheetConfig.TitleDataColumns != null && sheetConfig.TitleDataColumns.Count > 0)
{
rawTitleDataTable = new DataTable(sheetConfig.TableName);
rawTitleDataTable.FillSchemaByColumns(sheetConfig.TitleDataColumns);
//FinaChanCore.FillTableSchemaByColumns(sheetConfig.TitleDataColumns, rawTitleDataTable);
}
AsmHelper script = null;
if (!string.IsNullOrEmpty(sheetConfig.FilterScript) && !string.IsNullOrEmpty(sheetConfig.FilterMethod))
{
script = reportConfig.GetDynamicScript(sheetConfig.FilterScript);
}
e.GetRawSheetData(sheetConfig, rawDataTable, rawFormulaTable, rawTitleDataTable, script);
KPIReportData.RawDatas.Tables.Add(rawDataTable);
if (rawFormulaTable != null)
{
KPIReportData.RawDatas.Tables.Add(rawFormulaTable);
}
if (rawTitleDataTable != null)
{
KPIReportData.RawTitleDatas.Tables.Add(rawTitleDataTable);
}
FinaChanLogger.Info("Load raw data of {0} worksheet end", sheetConfig.Name);
}
});
#region Read Booking & CashCollections Raw Data
var excelHelper = new ExcelHelper(reportConfig.BookingCashWorkbookFile);
var bookingcashSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.BookingCashReport);
ReadRawSheetDatas(excelHelper, bookingcashSheetConfigs);
#endregion
#region Read Project Contribution(MTD) Raw Data
excelHelper = new ExcelHelper(reportConfig.ProjectContributionMTDWorkbookFile);
var projectContributionMTDSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.ProjectContributionMTDReport);
ReadRawSheetDatas(excelHelper, projectContributionMTDSheetConfigs);
#endregion
#region Read Project Contribution(TYD) Raw Data
excelHelper = new ExcelHelper(reportConfig.ProjectContributioYTDWorkbookFile);
var projectContributionYTDSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.ProjectContributionYTDReport);
ReadRawSheetDatas(excelHelper, projectContributionYTDSheetConfigs);
#endregion
#region Read BD Raw Data
excelHelper = new ExcelHelper(reportConfig.BDWorkbookFile);
var bdSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.BDReport);
ReadRawSheetDatas(excelHelper, bdSheetConfigs);
#endregion
#region Read Sector IDC (YTD)/SL IDC(YTD) Raw Data
excelHelper = new ExcelHelper(reportConfig.IDCProjectWorkbookFile);
var projectIDCSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.ProjectIDCReport);
ReadRawSheetDatas(excelHelper, projectIDCSheetConfigs);
#endregion
#region Read Oracle IDC by employee (YTD) Raw Data
excelHelper = new ExcelHelper(reportConfig.IDCEmployeeWorkbookFile);
var employeeIDCSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.EmployeeIDCReport);
ReadRawSheetDatas(excelHelper, employeeIDCSheetConfigs);
#endregion
#region Read Employee Activity Raw Data
excelHelper = new ExcelHelper(reportConfig.EmployeeWorkbookFile);
var employeeSheetConfigs = reportConfig.GetAllRawSheetConfigs().Where(sc => sc.FileType == RawSheetFileType.EmployeeReport);
ReadRawSheetDatas(excelHelper, employeeSheetConfigs);
#endregion
#endregion
#region Read Last Months KPI Data
KPIReportData.LastMonthKPIDatas = new DataSet();
if (!string.IsNullOrEmpty(reportConfig.LastMonthWorkbookFile))
{
excelHelper = new ExcelHelper(reportConfig.LastMonthWorkbookFile);
var sectorSheetConfigs = reportConfig.GetAllKPISheetConfigs()
.Where(c => c.ReportCatalog == ReportCatalog.Sector);
var slSheetConfigs = reportConfig.GetAllKPISheetConfigs()
.Where(c => c.ReportCatalog == ReportCatalog.SL);
foreach (var sheetConfig in sectorSheetConfigs)
{
FinaChanLogger.Info("Load lastmonth report data of {0} worksheet start", sheetConfig.Name);
if (sheetConfig != null && sheetConfig.DataColumns.Count > 0)
{
DataTable sectorTable = new DataTable(sheetConfig.TableName);
sectorTable.FillSchemaByColumns(sheetConfig.DataColumns);
// FinaChanCore.FillTableSchemaByColumns(sheetConfig.DataColumns, sectorTable);
DataTable sectorFormulaTable = null;
var containFormulaColumns = sheetConfig.DataColumns.Where(c => c.ContainFormula);
if (containFormulaColumns.Count() > 0)
{
sectorFormulaTable = new DataTable(string.Concat(sheetConfig.TableName, "Formulas"));
foreach (var column in containFormulaColumns)
{
sectorFormulaTable.Columns.Add(column.Name, typeof(string));
}
}
excelHelper.GetLastSectorKPIData(sheetConfig, reportConfig.ReportYear,
reportConfig.ReportMonth, sectorTable, sectorFormulaTable);
KPIReportData.LastMonthKPIDatas.Tables.Add(sectorTable);
if (sectorFormulaTable != null)
KPIReportData.LastMonthKPIDatas.Tables.Add(sectorFormulaTable);
}
FinaChanLogger.Info("Load lastmonth report data of {0} worksheet end", sheetConfig.Name);
}
foreach (var sheetConfig in slSheetConfigs)
{
FinaChanLogger.Info("Load lastmonth report data of {0} worksheet start", sheetConfig.Name);
if (sheetConfig != null && sheetConfig.DataColumns.Count > 0)
{
DataTable slTable = new DataTable(sheetConfig.TableName);
slTable.FillSchemaByColumns(sheetConfig.DataColumns);
//FinaChanCore.FillTableSchemaByColumns(sheetConfig.DataColumns, slTable);
DataTable slFormulaTable = null;
var containFormulaColumns = sheetConfig.DataColumns.Where(c => c.ContainFormula);
if (containFormulaColumns.Count() > 0)
{
slFormulaTable = new DataTable(string.Concat(sheetConfig.TableName, "Formulas"));
foreach (var column in containFormulaColumns)
{
slFormulaTable.Columns.Add(column.Name, typeof(string));
}
}
excelHelper.GetLastServiceLineKPIData(sheetConfig, reportConfig.ReportYear,
reportConfig.ReportMonth, slTable, slFormulaTable);
KPIReportData.LastMonthKPIDatas.Tables.Add(slTable);
if (slFormulaTable != null)
KPIReportData.LastMonthKPIDatas.Tables.Add(slFormulaTable);
}
FinaChanLogger.Info("Load lastmonth report data of {0} worksheet end", sheetConfig.Name);
}
}
#endregion
}
