public void GetLastSectorKPIData(KPISheetConfig sheetConfig, int reportYear, int reportMonth, DataTable sectorData, DataTable sectorFormulaData) { try { var sheet = workbook.Worksheets[sheetConfig.KPISheetIndex]; var columns = sheetConfig.DataColumns; for (int month = 1; month < reportMonth; month++) { DataRow newRow = sectorData.NewRow(); DataRow formulaRow = null; if (sectorFormulaData != null) { formulaRow = sectorFormulaData.NewRow(); } string cellName = null; object cellValue = null; foreach (var column in columns) { cellName = (month == 1) ? column.GetCell : GetNextCellinRow(sheet, column.GetCell, month - 2); cellValue = GetCellValue(sheet, column, cellName); newRow[column.Name] = cellValue; if (column.ContainFormula && formulaRow != null) { formulaRow[column.Name] = GetCellFormula(sheet, cellName); } } sectorData.Rows.Add(newRow); if (formulaRow != null) sectorFormulaData.Rows.Add(formulaRow); } } catch (System.Exception ex) { FinaChanLogger.Info("Occured error when load lastmonth report data of {0} worksheet, caused by: {1}, details: {2}", sheetConfig.Name, ex.Message, ex.StackTrace); throw ex; } }
private void FillKPISheetConfigs(XDocument doc, XNamespace ns) { var sheetElements = doc.Root.GetChild("KPISheets", ns).GetDescendants("Sheet", ns); foreach (var e in sheetElements) { var kpiSheetConfig = new KPISheetConfig() { Name = e.GetStringValue("Name"), TableName = e.GetStringValue("TableName"), KPISheetIndex = e.GetValue<Int32>("Index"), Independency = e.GetValue<Boolean>("Independency"), ReportCatalog = e.GetEnumValue<ReportCatalog>("ReportCatalog"), SLOracle = e.GetValue<Boolean>("SLOracle", false), TabColor = e.GetStringValue("TabColor", string.Empty), HeadCountFilterMethod = e.GetStringValue("HeadCountFilterMethod", string.Empty) }; var stylesElement = e.GetChild("Styles", ns); if (stylesElement != null) { var styleElements = stylesElement.GetDescendants("Style", ns); foreach (var styleElement in styleElements) { kpiSheetConfig.Styles.Add(new SheetStyle { CellName = styleElement.GetStringValue("Cell"), ColumnWidth = styleElement.GetNullableValue<Double>("ColumnWidth"), RowHeight = styleElement.GetNullableValue<Double>("RowHeight") }); } } var dataElement = e.GetChild("Data", ns); if (dataElement != null) { var colElements = dataElement.GetDescendants("Column", ns); foreach (var colElement in colElements) { kpiSheetConfig.DataColumns.Add(new KPISheetDataColumn { Name = colElement.GetStringValue("Name"), GetCell = colElement.GetStringValue("GetCell"), SetCell = colElement.GetStringValue("SetCell"), Type = colElement.GetEnumValue<ColumnType>("Type"), DataType = colElement.GetEnumValue<DataType>("DataType", DataType.String), ContainFormula = colElement.GetValue<Boolean>("ContainFormula", false), FilterScript = colElement.GetStringValue("FilterScript", string.Empty), FilterExp = colElement.GetStringValue("FilterExp", string.Empty) }); } } var formulasElement = e.GetChild("Formulas", ns); if (formulasElement != null) { var templateFormulas = GetTemplateFormulas(formulasElement, ns); kpiSheetConfig.Formulas = GetFormulas(formulasElement, templateFormulas, ns); } var hideColumnsElement = e.GetChild("HideColumns", ns); if (hideColumnsElement != null) { kpiSheetConfig.HideColumns = GetHideColumns(hideColumnsElement, ns); } AddKPISheetConfig(kpiSheetConfig); } }
private void AddKPISheetConfig(KPISheetConfig kpiSheetConfig) { kpiSheetConfigs.Add(kpiSheetConfig); }
public void SetLastMonthKPIRefData(KPISheetConfig sheetConfig, int reportMonth, DataTable lastMonthKPIData, DataTable lastMonthFormulaData) { try { Worksheet worksheet = workbook.Worksheets[sheetConfig.Name]; var columns = sheetConfig.DataColumns; string cellName = null; for (int month = 1; month < reportMonth; month++) { FinaChanLogger.Info("Fill last month {0} kpi ref data start"); var rowIndex = month - 1; var lastMonthKPIRow = lastMonthKPIData.Rows[rowIndex]; foreach (var column in columns) { cellName = (month == 1) ? column.SetCell : ExcelHelper.GetNextCellinRow(worksheet, column.SetCell, month - 2); if (column.ContainFormula && lastMonthFormulaData != null && lastMonthFormulaData.Rows[rowIndex][column.Name].ToString().Length > 0) { SetCellFormula(worksheet, cellName, (string)lastMonthFormulaData.Rows[rowIndex][column.Name]); } else { SetCellValue(worksheet, cellName, lastMonthKPIRow[column.Name]); } FinaChanLogger.Info("Fill {0} ref data: {1} into cell {2}", column.Name, lastMonthKPIRow[column.Name], cellName); } FinaChanLogger.Info("Fill last month {0} kpi ref data end"); } } catch (System.Exception ex) { FinaChanLogger.Info("Occured error when fill calculated kpi data into {0} worksheet, caused by: {1}, details: {2}", sheetConfig.Name, ex.Message, ex.StackTrace); throw ex; } }
private void FillSectorData(KPISheetConfig sheetConfig, DataTable sectorTable) { FinaChanLogger.Info("Calculate kpi data of {0} worksheet start", sheetConfig.Name); try { DataRow newRow = sectorTable.NewRow(); var dataColumns = sheetConfig.DataColumns; #region Calculate Booking Value double bookingValue = 0; var bookingColumnConfig = dataColumns.Single(c => c.Name.ToUpper() == "BOOKING"); var bookingFilterScript = reportConfig.GetDynamicScript(bookingColumnConfig.FilterScript); var bookingFilterExp = (Func<string, bool>)bookingFilterScript.Invoke(bookingColumnConfig.FilterExp); var boolingRows = KPIReportData.RawDatas.Tables["Bookings"].Rows.Cast<DataRow>() .Where(r => bookingFilterExp(r["Sector"].ToString().ToUpper())); FinaChanLogger.Info("Booking RowCount: {0}", boolingRows.Count()); bookingValue += boolingRows.Sum(r => Convert.ToDouble(r["Converted Bkg(CNY)"])); newRow["Booking"] = Convert.ToInt32(Math.Round(bookingValue / 1000, 0)); FinaChanLogger.Info("Booking: {0}", newRow["Booking"]); #endregion #region Calculate SoldCM Value double soldCMValue = 0; var soldcmColumn = dataColumns.Single(c => c.Name.ToUpper() == "SOLDCM"); var soldcmFilterScript = reportConfig.GetDynamicScript(soldcmColumn.FilterScript); var soldcmFilterExp = (Func<string, bool>)soldcmFilterScript.Invoke(soldcmColumn.FilterExp); var soldCMRows = KPIReportData.RawDatas.Tables["Bookings"].Rows.Cast<DataRow>() .Where(r => soldcmFilterExp(r["Sector"].ToString().ToUpper())); FinaChanLogger.Info("SoldCM RowCount in Bookings(YTD): {0}", soldCMRows.Count()); soldCMValue += soldCMRows.Sum(r => Convert.ToDouble(r["CM"])); newRow["SoldCM"] = Convert.ToInt32(Math.Round(soldCMValue / 1000, 0)); FinaChanLogger.Info("SoldCM: {0}", newRow["SoldCM"]); #endregion #region Calculate Revenue Value double revenueValue = 0; var revenueColumn = dataColumns.Single(c => c.Name.ToUpper() == "REVENUE"); var revenueFilterScript = reportConfig.GetDynamicScript(revenueColumn.FilterScript); var revenueFilterExp = (Func<string, bool>)revenueFilterScript.Invoke(revenueColumn.FilterExp); var revenueRows = KPIReportData.RawDatas.Tables["ProjectContributionMTD"].Rows.Cast<DataRow>() .Where(r => revenueFilterExp(r["Sector"].ToString().ToUpper())); FinaChanLogger.Info("Revenue RowCount in Project Contribution(MTD): {0}", revenueRows.Count()); revenueValue += revenueRows.Sum(r => Convert.ToDouble(r["Total Revenue"])); newRow["Revenue"] = Convert.ToInt32(Math.Round(revenueValue, 0)); FinaChanLogger.Info("Revenue: {0}", newRow["Revenue"]); #endregion #region Calculate BDCost Value double bdCostValue = 0; var bdcostColumn = dataColumns.Single(c => c.Name.ToUpper() == "BDCOST"); var bdcostFilterScript = reportConfig.GetDynamicScript(bdcostColumn.FilterScript); var bdcostFilterExp = (Func<string, bool>)bdcostFilterScript.Invoke(bdcostColumn.FilterExp); var bdYTDRows = KPIReportData.RawDatas.Tables["BD"].Rows.Cast<DataRow>() .Where(r => bdcostFilterExp(r["Project Organization"].ToString().ToUpper())); FinaChanLogger.Info("BDCost RowCount in BD(YTD): {0}", bdYTDRows.Count()); bdCostValue += bdYTDRows.Sum(r => Convert.ToDouble(r["Total Cost"])); var secotrIDCRows = KPIReportData.RawDatas.Tables["SectorIDC"].Rows.Cast<DataRow>() .Where(r => bdcostFilterExp(r["Project Organization"].ToString().ToUpper())); FinaChanLogger.Info("BDCost RowCount in Sector IDC(YTD): {0}", secotrIDCRows.Count()); bdCostValue += secotrIDCRows.Sum(r => Convert.ToDouble(r["Total Cost"])); newRow["BDCost"] = Convert.ToInt32(-bdCostValue / 1000); FinaChanLogger.Info("BDCost: {0}", newRow["BDCost"]); #endregion #region Calculate CashCollections Value double cashCollectionsValue = 0; var cashColumn = dataColumns.Single(c => c.Name.ToUpper() == "CASHCOLLECTIONS"); var cashFilterScript = reportConfig.GetDynamicScript(cashColumn.FilterScript); var cashFilterExp = (Func<string, bool>)cashFilterScript.Invoke(cashColumn.FilterExp); var cashCollectionRows = KPIReportData.RawDatas.Tables["CashCollection"].Rows.Cast<DataRow>() .Where(r => cashFilterExp(r["Sector"].ToString().ToUpper())); FinaChanLogger.Info("CashCollections RowCount in Cash Collection(MTD): {0}", cashCollectionRows.Count()); cashCollectionsValue += cashCollectionRows.Sum(r => Convert.ToDouble(r["in RMB"])); newRow["CashCollections"] = Convert.ToInt32(Math.Round(cashCollectionsValue / 1000, 0)); FinaChanLogger.Info("CashCollections: {0}", newRow["CashCollections"]); #endregion sectorTable.Rows.Add(newRow); } catch (System.Exception ex) { FinaChanLogger.Info("Occured error when calculate kpi data of {0} worksheet, caused by: {1}, details: {2}", sheetConfig.Name, ex.Message, ex.StackTrace); throw ex; } finally { FinaChanLogger.Info("Calculate kpi data of {0} worksheet end", sheetConfig.Name); } }
private void FillOracleSLData(KPISheetConfig sheetConfig, DataTable slTable, IList<string> employeeNames, int headCount, int totalEmployeeCount, double nonEmployeeIDC) { FinaChanLogger.Info("Calculate kpi data of {0} worksheet start", sheetConfig.Name); try { DataRow newRow = slTable.NewRow(); var dataColumns = sheetConfig.DataColumns; #region Calculate Booking Value double bookingValue = 0; var bookingColumn = dataColumns.Single(c => c.Name.ToUpper() == "BOOKING"); var bookingFilterScript = reportConfig.GetDynamicScript(bookingColumn.FilterScript); var bookingFilterExp = (Func<string, bool>)bookingFilterScript.Invoke(bookingColumn.FilterExp); var bookingRows = KPIReportData.RawDatas.Tables["Bookings"].Rows.Cast<DataRow>() .Where(r => bookingFilterExp(r["Service Line"].ToString().ToUpper())); FinaChanLogger.Info("Booking RowCount in Bookings(YTD): {0}", bookingRows.Count()); bookingValue += bookingRows.Sum(r => Convert.ToDouble(r["Converted Bkg(CNY)"])); newRow["Booking"] = Convert.ToInt32(bookingValue / 1000); FinaChanLogger.Info("Booking: {0}", newRow["Booking"]); #endregion #region Calculate Revenue Value double revenueValue = 0; var revenueColumn = dataColumns.Single(c => c.Name.ToUpper() == "REVENUE"); var revenueFilterScript = reportConfig.GetDynamicScript(revenueColumn.FilterScript); var revenueFilterExp = (Func<string, bool>)revenueFilterScript.Invoke(revenueColumn.FilterExp); var revenueRows = KPIReportData.RawDatas.Tables["ProjectContributionMTD"].Rows.Cast<DataRow>() .Where(r => revenueFilterExp(r["Service Line"].ToString().ToUpper())); FinaChanLogger.Info("Revenue RowCount in Project Contribution(MTD): {0}", revenueRows.Count()); revenueValue += revenueRows.Sum(r => Convert.ToDouble(r["Total Revenue"])); newRow["Revenue"] = Convert.ToInt32(Math.Round(revenueValue, 0)); FinaChanLogger.Info("Revenue: {0}", newRow["Revenue"]); #endregion #region Calculate CM Value double cmValue = 0; var cmColumn = dataColumns.Single(c => c.Name.ToUpper() == "CM"); var cmFilterScript = reportConfig.GetDynamicScript(cmColumn.FilterScript); var cmFilterExp = (Func<string, bool>)cmFilterScript.Invoke(cmColumn.FilterExp); var cmRows = KPIReportData.RawDatas.Tables["ProjectContributionMTD"].Rows.Cast<DataRow>() .Where(r => cmFilterExp(r["Service Line"].ToString().ToUpper())); FinaChanLogger.Info("CM RowCount in Project Contribution(MTD): {0}", cmRows.Count()); cmValue += cmRows.Sum(r => Convert.ToDouble(r["Total CM"])); newRow["CM"] = Convert.ToInt32(Math.Round(cmValue, 0)); FinaChanLogger.Info("CM: {0}", newRow["CM"]); #endregion #region Calculate IDC Value var idcColumn = dataColumns.Single(c => c.Name.ToUpper() == "IDC"); var idcFilterScript = reportConfig.GetDynamicScript(idcColumn.FilterScript); var idcFilterExp = (Func<string, bool>)idcFilterScript.Invoke(idcColumn.FilterExp); var idcRows = KPIReportData.RawDatas.Tables["OracleEmployeeIDC"].Rows.Cast<DataRow>() .Where(r => idcFilterExp(r["Expenditure Organization"].ToString().ToUpper())); FinaChanLogger.Info("IDC RowsCount: {0}", idcRows.Count()); //var employeeIDCRows = from idcRow in idcRows // where employeeNames.Contains(idcRow["Employee/Supplier Name"].ToString().ToUpper()) // select idcRow; var employeeIDCRows = from idcRow in idcRows where (idcRow["Expenditure Organization"].ToString().ToUpper().IndexOf("PEOPLESOFT_SIEBEL") == -1) select idcRow; FinaChanLogger.Info("IDC for Employee RowsCount: {0}", employeeIDCRows.Count()); double employeeIDCValue = employeeIDCRows.Sum(r => Convert.ToDouble(r["Total Cost"])); double nonEmployeeIDCValue = nonEmployeeIDC * ((double)headCount / (double)totalEmployeeCount); FinaChanLogger.Info("IDC for Employee: {0}", employeeIDCValue); FinaChanLogger.Info("IDC for NoneEmployee: {0}", nonEmployeeIDCValue); newRow["IDC"] = -Convert.ToInt32(Math.Round((employeeIDCValue + nonEmployeeIDCValue) / 1000, 0)); FinaChanLogger.Info("IDC: {0}", newRow["IDC"]); #endregion #region Calculate Cash Collections Value double cashCollectionsValue = 0; var cashColumn = dataColumns.Single(c => c.Name.ToUpper() == "CASHCOLLECTIONS"); var cashFilterScript = reportConfig.GetDynamicScript(cashColumn.FilterScript); var cashFilterExp = (Func<string, bool>)cashFilterScript.Invoke(cashColumn.FilterExp); var cashCollectionRows = KPIReportData.RawDatas.Tables["CashCollection"].Rows.Cast<DataRow>() .Where(r => cashFilterExp(r["Service Line"].ToString().ToUpper())); FinaChanLogger.Info("CashCollections RowCount in Cash Collection(MTD): {0}", cashCollectionRows.Count()); cashCollectionsValue += cashCollectionRows.Sum(r => Convert.ToDouble(r["in RMB"])); newRow["CashCollections"] = Convert.ToInt32(Math.Round(cashCollectionsValue / 1000, 0)); FinaChanLogger.Info("CashCollections: {0}", newRow["CashCollections"]); #endregion slTable.Rows.Add(newRow); } catch (System.Exception ex) { FinaChanLogger.Info("Occured error when calculate kpi data of {0} worksheet, caused by: {1}, details: {2}", sheetConfig.Name, ex.Message, ex.StackTrace); throw ex; } finally { FinaChanLogger.Info("Calculate kpi data of {0} worksheet end", sheetConfig.Name); } }
private void FillNoneOracleSLData(KPISheetConfig sheetConfig, DataTable slTable) { FinaChanLogger.Info("Calculate kpi data of {0} worksheet start", sheetConfig.Name); try { DataRow newRow = slTable.NewRow(); var dataColumns = sheetConfig.DataColumns; #region Calculate Booking Value double bookingValue = 0; var bookingColumn = dataColumns.Single(c => c.Name.ToUpper() == "BOOKING"); var bookingFilterScript = reportConfig.GetDynamicScript(bookingColumn.FilterScript); var bookingFilterExp = (Func<string, bool>)bookingFilterScript.Invoke(bookingColumn.FilterExp); var boolingRows = KPIReportData.RawDatas.Tables["Bookings"].Rows.Cast<DataRow>() .Where(r => bookingFilterExp(r["Service Line"].ToString().ToUpper())); FinaChanLogger.Info("Booking RowCount in Bookings(YTD): {0}", boolingRows.Count()); bookingValue += boolingRows.Sum(r => Convert.ToDouble(r["Converted Bkg(CNY)"])); newRow["Booking"] = Convert.ToInt32(bookingValue / 1000); FinaChanLogger.Info("Booking: {0}", newRow["Booking"]); #endregion #region Calculate Revenue Value double revenueValue = 0; var revenueColumn = dataColumns.Single(c => c.Name.ToUpper() == "REVENUEBEFOREADJUSTMENT"); var revenueFilterScript = reportConfig.GetDynamicScript(revenueColumn.FilterScript); var revenueFilterExp = (Func<string, bool>)revenueFilterScript.Invoke(revenueColumn.FilterExp); var revenueRows = KPIReportData.RawDatas.Tables["ProjectContributionMTD"].Rows.Cast<DataRow>() .Where(r => revenueFilterExp(r["Service Line"].ToString().ToUpper())); FinaChanLogger.Info("Revenue RowCount in Project Contribution(MTD): {0}", revenueRows.Count()); revenueValue += revenueRows.Sum(r => Convert.ToDouble(r["Total Revenue"])); newRow["RevenueBeforeAdjustment"] = Convert.ToInt32(Math.Round(revenueValue, 0)); FinaChanLogger.Info("Revenue: {0}", newRow["RevenueBeforeAdjustment"]); #endregion #region Calculate CM Value double cmValue = 0; var cmColumn = dataColumns.Single(c => c.Name.ToUpper() == "CMBEFOREADJUSTMENT"); var cmFilterScript = reportConfig.GetDynamicScript(cmColumn.FilterScript); var cmFilterExp = (Func<string, bool>)cmFilterScript.Invoke(cmColumn.FilterExp); var cmRows = KPIReportData.RawDatas.Tables["ProjectContributionMTD"].Rows.Cast<DataRow>() .Where(r => cmFilterExp(r["Service Line"].ToString().ToUpper())); FinaChanLogger.Info("CM RowCount in Project Contribution(MTD): {0}", cmRows.Count()); cmValue += cmRows.Sum(r => Convert.ToDouble(r["Total CM"])); newRow["CMBeforeAdjustment"] = Convert.ToInt32(Math.Round(cmValue, 0)); FinaChanLogger.Info("CM: {0}", newRow["CMBEFOREADJUSTMENT"]); #endregion #region Calcuate IDC Value double idcValue = 0; var idcColumn = dataColumns.Single(c => c.Name.ToUpper() == "IDC"); var idcFilterScript = reportConfig.GetDynamicScript(idcColumn.FilterScript); var idcFilterExp = (Func<string, bool>)idcFilterScript.Invoke(idcColumn.FilterExp); var idcRows = KPIReportData.RawDatas.Tables["SLIDC"].Rows.Cast<DataRow>() .Where(r => idcFilterExp(r["Project Organization"].ToString().ToUpper())); FinaChanLogger.Info("IDC RowCount in SL IDC(YTD): {0}", idcRows.Count()); idcValue += idcRows.Sum(r => Convert.ToDouble(r["Total Cost"])); newRow["IDC"] = -Convert.ToInt32(Math.Round(idcValue / 1000, 0)); FinaChanLogger.Info("IDC: {0}", newRow["IDC"]); #endregion #region Calculate Cash Collection Value double cashCollectionsValue = 0; var cashColumn = dataColumns.Single(c => c.Name.ToUpper() == "CASHCOLLECTIONS"); var cashFilterScript = reportConfig.GetDynamicScript(cashColumn.FilterScript); var cashFilterExp = (Func<string, bool>)cashFilterScript.Invoke(cashColumn.FilterExp); var cashCollectionRows = KPIReportData.RawDatas.Tables["CashCollection"].Rows.Cast<DataRow>() .Where(r => cashFilterExp(r["Service Line"].ToString().ToUpper())); FinaChanLogger.Info("CashCollections RowCount in Cash Collection(MTD): {0}", cashCollectionRows.Count()); cashCollectionsValue += cashCollectionRows.Sum(r => Convert.ToDouble(r["in RMB"])); newRow["CashCollections"] = Convert.ToInt32(Math.Round(cashCollectionsValue / 1000, 0)); FinaChanLogger.Info("CashCollections: {0}", newRow["CashCollections"]); #endregion slTable.Rows.Add(newRow); } catch (System.Exception ex) { FinaChanLogger.Info("Occured error when calculate kpi data of {0} worksheet, caused by: {1}, details: {2}", sheetConfig.Name, ex.Message, ex.StackTrace); throw ex; } finally { FinaChanLogger.Info("Calculate kpi data of {0} worksheet end", sheetConfig.Name); } }