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);
}
}
