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 }