/// <summary>
/// 누적확률 분포적용 엑셀 다운로드
/// </summary>
public void SetNormDistExcelDown()
{
MicroBSC.BSC.Biz.Biz_Bsc_Term_Detail objBSC = new MicroBSC.BSC.Biz.Biz_Bsc_Term_Detail();
DataSet rDs = objBSC.GetOriginalScorePerKPI(this.IEstTermRefID, this.IYMD);
int intRow = rDs.Tables[0].Rows.Count;
int intCol = rDs.Tables[0].Columns.Count;
if (intRow < 1)
{
return;
}
ExcelEngine xlsEgn = new ExcelEngine(); //Step 2 : Instantiate the excel xlsApp object.
IApplication xlsApp = xlsEgn.Excel;
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = xlsEgn.Excel.Workbooks.Create(3);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet xlsSht = workbook.Worksheets[0];
string strQrtMark = "\"";
string strColName = "";
string strColEsttermRefID = "";
string strColKpiRefID = "";
string strColYMD = "";
string strColUseMS = "";
string strColOriMS = "";
string strColAvgMS = "";
string strColStdMS = "";
string strColAdaMS = "";
string strColAdsMS = "";
string strColNorMS = "";
string strColFnlMS = "";
string strColUseTS = "";
string strColOriTS = "";
string strColAvgTS = "";
string strColStdTS = "";
string strColAdaTS = "";
string strColAdsTS = "";
string strColNorTS = "";
string strColFnlTS = "";
int itxr_user = gUserInfo.Emp_Ref_ID;
MicroBSC.BSC.Biz.Biz_Bsc_Kpi_Score_Detail objSCR = new MicroBSC.BSC.Biz.Biz_Bsc_Kpi_Score_Detail();
for (int i = 0; i < intCol; i++)
{
strColName = WebCommon.GetExcelColumnName(i) + Convert.ToString(1);
xlsSht.Range[strColName].Text = rDs.Tables[0].Columns[i].ColumnName;
xlsSht.Range[strColName].ColumnWidth = 10;
}
for (int i = 0; i < intRow; i++)
{
for (int j = 0; j < intCol; j++)
{
strColName = WebCommon.GetExcelColumnName(j) + Convert.ToString(i + 2);
switch (rDs.Tables[0].Columns[j].DataType.Name)
{
case ("Decimal"):
xlsSht.Range[strColName].Number = Convert.ToDouble(rDs.Tables[0].Rows[i][j].ToString());
break;
case ("Double"):
xlsSht.Range[strColName].Number = Convert.ToDouble(rDs.Tables[0].Rows[i][j].ToString());
break;
case ("Int16"):
xlsSht.Range[strColName].Number = Convert.ToInt16(rDs.Tables[0].Rows[i][j].ToString());
break;
case ("Int32"):
xlsSht.Range[strColName].Number = Convert.ToInt32(rDs.Tables[0].Rows[i][j].ToString());
break;
case ("Int64"):
xlsSht.Range[strColName].Number = Convert.ToInt64(rDs.Tables[0].Rows[i][j].ToString());
break;
default:
xlsSht.Range[strColName].Text = rDs.Tables[0].Rows[i][j].ToString();
break;
}
if (j == (intCol - 1))
{
strColEsttermRefID = WebCommon.GetExcelColumnName(0) + Convert.ToString(i + 2);
strColKpiRefID = WebCommon.GetExcelColumnName(1) + Convert.ToString(i + 2);
strColYMD = WebCommon.GetExcelColumnName(2) + Convert.ToString(i + 2);
strColUseMS = WebCommon.GetExcelColumnName(3) + Convert.ToString(i + 2);
strColOriMS = WebCommon.GetExcelColumnName(4) + Convert.ToString(i + 2);
strColAvgMS = WebCommon.GetExcelColumnName(5) + Convert.ToString(i + 2);
strColStdMS = WebCommon.GetExcelColumnName(6) + Convert.ToString(i + 2);
strColAdaMS = WebCommon.GetExcelColumnName(7) + Convert.ToString(i + 2);
strColAdsMS = WebCommon.GetExcelColumnName(8) + Convert.ToString(i + 2);
strColNorMS = WebCommon.GetExcelColumnName(9) + Convert.ToString(i + 2);
strColFnlMS = WebCommon.GetExcelColumnName(10) + Convert.ToString(i + 2);
strColUseTS = WebCommon.GetExcelColumnName(11) + Convert.ToString(i + 2);
strColOriTS = WebCommon.GetExcelColumnName(12) + Convert.ToString(i + 2);
strColAvgTS = WebCommon.GetExcelColumnName(13) + Convert.ToString(i + 2);
strColStdTS = WebCommon.GetExcelColumnName(14) + Convert.ToString(i + 2);
strColAdaTS = WebCommon.GetExcelColumnName(15) + Convert.ToString(i + 2);
strColAdsTS = WebCommon.GetExcelColumnName(16) + Convert.ToString(i + 2);
strColNorTS = WebCommon.GetExcelColumnName(17) + Convert.ToString(i + 2);
strColFnlTS = WebCommon.GetExcelColumnName(18) + Convert.ToString(i + 2);
//=IF(IF(D739="N",E739,(85+(J739-0.5)*30))<70,0, IF(D739="N",E739,(85+(J739-0.5)*30))) --최초계산식
//=IF(E11<1,0, IF(D11="N",E11,(85+(J11-0.5)*30)))
//=IF(D739="N",E739, IF(G11=0, 85, (85+(J739-0.5)*30)))
xlsSht.Range[strColNorMS].Formula = "=IF(" + strColStdMS + "=0,0,ROUNDDOWN(NORMDIST(" + strColOriMS + "," + strColAvgMS + "," + strColStdMS + ",TRUE),8))";
xlsSht.Range[strColNorTS].Formula = "=IF(" + strColStdTS + "=0,0,ROUNDDOWN(NORMDIST(" + strColOriTS + "," + strColAvgTS + "," + strColStdTS + ",TRUE),8))";
//xlsSht.Range[strColFnlMS].Formula = "=IF(IF("+strColUseMS+"="+strQrtMark+"N"+strQrtMark+","+strColOriMS+",(85+("+strColNorMS+"-0.5)*30))<70,0, IF("+strColUseMS+"="+strQrtMark+"N"+strQrtMark+","+strColOriMS+",(85+("+strColNorMS+"-0.5)*30)))";
//xlsSht.Range[strColFnlTS].Formula = "=IF(IF("+strColUseMS+"="+strQrtMark+"N"+strQrtMark+","+strColOriTS+",(85+("+strColNorTS+"-0.5)*30))<70,0, IF("+strColUseTS+"="+strQrtMark+"N"+strQrtMark+","+strColOriTS+",(85+("+strColNorTS+"-0.5)*30)))";
//xlsSht.Range[strColFnlMS].Formula = "=IF("+strColStdMS+"=0, 85, IF("+strColUseMS+"="+strQrtMark+"N"+strQrtMark+","+strColOriMS+",(85+("+strColNorMS+"-0.5)*30)))";
//xlsSht.Range[strColFnlTS].Formula = "=IF("+strColStdTS+"=0, 85, IF("+strColUseTS+"="+strQrtMark+"N"+strQrtMark+","+strColOriTS+",(85+("+strColNorTS+"-0.5)*30)))";
xlsSht.Range[strColFnlMS].Formula = "=IF([" + strColUseMS + "]=" + strQrtMark + "N" + strQrtMark + ",[" + strColOriMS + "], IF([" + strColStdMS + "]=0, 85, (85+([" + strColNorMS + "]-0.5)*30)))";
xlsSht.Range[strColFnlTS].Formula = "=IF([" + strColUseTS + "]=" + strQrtMark + "N" + strQrtMark + ",[" + strColOriTS + "], IF([" + strColStdTS + "]=0, 85, (85+([" + strColNorTS + "]-0.5)*30)))";
objSCR.Iestterm_ref_id = Convert.ToInt32(xlsSht.Range[strColEsttermRefID].Text);
objSCR.Ikpi_ref_id = Convert.ToInt32(xlsSht.Range[strColKpiRefID].Text);
objSCR.Iymd = xlsSht.Range[strColYMD].Text;
objSCR.Inrmdst_use_ms = xlsSht.Range[strColUseMS].Text;
objSCR.Ipoints_ori_ms = xlsSht.Range[strColOriMS].Number.ToString();
objSCR.Ipoints_avg_ms = xlsSht.Range[strColAvgMS].Number.ToString();
objSCR.Ipoints_std_ms = xlsSht.Range[strColStdMS].Number.ToString();
objSCR.Ipoints_ada_ms = xlsSht.Range[strColAdaMS].Number.ToString();
objSCR.Ipoints_ads_ms = xlsSht.Range[strColAdsMS].Number.ToString();
objSCR.Ipoints_nor_ms = xlsSht.Range[strColNorMS].Number.ToString();
objSCR.Ipoints_fnl_ms = xlsSht.Range[strColFnlMS].Number.ToString();
objSCR.Inrmdst_use_ts = xlsSht.Range[strColUseTS].Text;
objSCR.Ipoints_ori_ts = xlsSht.Range[strColOriTS].Number.ToString();
objSCR.Ipoints_avg_ts = xlsSht.Range[strColAvgTS].Number.ToString();
objSCR.Ipoints_std_ts = xlsSht.Range[strColStdTS].Number.ToString();
objSCR.Ipoints_ada_ts = xlsSht.Range[strColAdaTS].Number.ToString();
objSCR.Ipoints_ads_ts = xlsSht.Range[strColAdsTS].Number.ToString();
objSCR.Ipoints_nor_ts = xlsSht.Range[strColNorTS].Number.ToString();
objSCR.Ipoints_fnl_ts = xlsSht.Range[strColFnlTS].Number.ToString();
//intRtn = objSCR.InsertData
// ( objSCR.Iestterm_ref_id
// , objSCR.Ikpi_ref_id
// , objSCR.Iymd
// , objSCR.Inormdist_use_yn
// , objSCR.Ipoints_ori_ms
// , objSCR.Ipoints_avg_ms
// , objSCR.Ipoints_std_ms
// , objSCR.Ipoints_ada_ms
// , objSCR.Ipoints_ads_ms
// , objSCR.Ipoints_nor_ms
// , objSCR.Ipoints_fnl_ms
// , objSCR.Ipoints_ori_ms
// , objSCR.Ipoints_avg_ms
// , objSCR.Ipoints_std_ms
// , objSCR.Ipoints_ada_ms
// , objSCR.Ipoints_ads_ms
// , objSCR.Ipoints_nor_ms
// , objSCR.Ipoints_fnl_ms
// , itxr_user);
//objBSC.Iestterm_ref_id = sheet.Range[strColEsttermRefID].FormulaNumberValue.ToString();
//string strTmp = xlsSht.Range[strColNorm].FormulaNumberValue.ToString();
}
}
}
# region Static Data
# region Formatting results
//IStyle headerStyle = workbook.Styles.Add("Heading", null);
////Add custom colors to the palette.
//workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33));
//headerStyle.Color = Color.FromArgb(255, 174, 33);
//headerStyle.Font.Bold = true;
//headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
//headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
//headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
//headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
////Body Style
//IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
////Add custom colors to the palette.
//workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247));
//workbook.SetPaletteColor(10, Color.FromArgb(204, 212, 230));
//bodyStyle.Color = Color.FromArgb(239, 243, 247);
//bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
//bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
//bodyStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
//bodyStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
//bodyStyle.Borders.ColorRGB = Color.FromArgb(204, 212, 230);
//bodyStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
////Formatting Results
//xlsSht.Range["A1:B144"].CellStyleName = "BodyStyle";
//xlsSht.Range["A1:A2"].CellStyleName = "Heading";
//xlsSht.Range["B2"].CellStyleName = "Heading";
//xlsSht.Range["A21"].CellStyleName = "Heading";
//xlsSht.Range["B21"].CellStyleName = "Heading";
# endregion Formatting Reults
# endregion
/// <summary>
/// 최종외부점수계산 / 최종점수 Rollup
/// </summary>
public void CalcFinalExternalScore()
{
MicroBSC.BSC.Biz.Biz_Bsc_Kpi_Score_Detail objBSC = new MicroBSC.BSC.Biz.Biz_Bsc_Kpi_Score_Detail();
int iRtn = objBSC.UpdateExternalOriScore(this.IEstTermRefID, this.IYMD);
CalcQuick objCAL = new CalcQuick();
DataSet rDs = objBSC.GetExternalScorePerKPI(this.IEstTermRefID, this.IYMD);
int intRow = rDs.Tables[0].Rows.Count;
int intCol = rDs.Tables[0].Columns.Count;
int intRtn = 0;
string strQrtMark = "\"";
string sQM = "\"";
int intEsttermRefID = 0;
int intKpiRefID = 0;
string strYMD = "";
string strUseTS = "";
string strOriTS = "";
string strAvgTS = "";
string strStdTS = "";
string strAdaTS = "";
string strAdsTS = "";
string strNorTS = "";
string strFnlTS = "";
string strColEsttermRefID = "";
string strColKpiRefID = "";
string strColYMD = "";
string strColUseTS = "";
string strColOriTS = "";
string strColAvgTS = "";
string strColStdTS = "";
string strColAdaTS = "";
string strColAdsTS = "";
string strColNorTS = "";
string strColFnlTS = "";
int itxr_user = gUserInfo.Emp_Ref_ID;
DataRow dr;
for (int i = 0; i < intRow; i++)
{
if (i == 0)
{
strColEsttermRefID = rDs.Tables[0].Columns[0].ColumnName;
strColKpiRefID = rDs.Tables[0].Columns[1].ColumnName;
strColYMD = rDs.Tables[0].Columns[2].ColumnName;
strColUseTS = rDs.Tables[0].Columns[3].ColumnName;
strColOriTS = rDs.Tables[0].Columns[4].ColumnName;
strColAvgTS = rDs.Tables[0].Columns[5].ColumnName;
strColStdTS = rDs.Tables[0].Columns[6].ColumnName;
strColAdaTS = rDs.Tables[0].Columns[7].ColumnName;
strColAdsTS = rDs.Tables[0].Columns[8].ColumnName;
strColNorTS = rDs.Tables[0].Columns[9].ColumnName;
strColFnlTS = rDs.Tables[0].Columns[10].ColumnName;
}
dr = rDs.Tables[0].Rows[i];
intEsttermRefID = Convert.ToInt32(dr["ESTTERM_REF_ID"].ToString());
intKpiRefID = Convert.ToInt32(dr["KPI_REF_ID"].ToString());
strYMD = Convert.ToString(dr["YMD"].ToString());
strUseTS = Convert.ToString(dr["NRMDST_USE_TS"].ToString());
strOriTS = Convert.ToString(dr["POINTS_ORI_TS"].ToString());
strAvgTS = Convert.ToString(dr["POINTS_AVG_TS"].ToString());
strStdTS = Convert.ToString(dr["POINTS_STD_TS"].ToString());
strAdaTS = Convert.ToString(dr["POINTS_ADA_TS"].ToString());
strAdsTS = Convert.ToString(dr["POINTS_ADS_TS"].ToString());
strNorTS = Convert.ToString(dr["POINTS_NOR_TS"].ToString());
strFnlTS = Convert.ToString(dr["POINTS_FNL_TS"].ToString());
objCAL[strColEsttermRefID] = intEsttermRefID.ToString();
objCAL[strColKpiRefID] = intKpiRefID.ToString();
objCAL[strColYMD] = strYMD;
objCAL[strColUseTS] = strUseTS;
objCAL[strColOriTS] = strOriTS;
objCAL[strColAvgTS] = strAvgTS;
objCAL[strColStdTS] = strStdTS;
objCAL[strColAdaTS] = strAdaTS;
objCAL[strColAdsTS] = strAdsTS;
objCAL[strColNorTS] = strNorTS;
objCAL[strColFnlTS] = strFnlTS;
//objCAL[strColNorMS] = "=IF(["+strColStdMS+"]=0,0,ROUNDDOWN(NORMDIST(["+strColOriMS+"],["+strColAvgMS+"],["+strColStdMS+"],TRUE),8))";
objCAL[strColNorTS] = "=ROUNDDOWN(NORMDIST([" + strColOriTS + "],[" + strColAvgTS + "],[" + strColStdTS + "],TRUE),8)";
//objCAL[strColNorMS] = "=IF([" + strColUseMS + "]=" + sQM + "Y" + sQM + ",IF([" + strColStdMS + "]=0,0,ROUNDDOWN(NORMDIST(IF([" + strColAplBS + "]=" + sQM + "Y" + sQM + ",[" + strColAdsMS + "],[" + strColOriMS + "]),[" + strColAvgMS + "],[" + strColStdMS + "],TRUE),8)),IF([" + strColAplBS + "]=" + sQM + "Y" + sQM + ",[" + strColAdsMS + "],[" + strColOriMS + "]))";
//objCAL[strColNorTS] = "=IF([" + strColUseTS + "]=" + sQM + "Y" + sQM + ",IF([" + strColStdTS + "]=0,0,ROUNDDOWN(NORMDIST(IF([" + strColAplBS + "]=" + sQM + "Y" + sQM + ",[" + strColAdsTS + "],[" + strColOriTS + "]),[" + strColAvgTS + "],[" + strColStdTS + "],TRUE),8)),IF([" + strColAplBS + "]=" + sQM + "Y" + sQM + ",[" + strColAdsTS + "],[" + strColOriTS + "]))";
//objCAL[strColFnlMS] = "=IF(IF(["+strColUseMS+"]="+strQrtMark+"N"+strQrtMark+",["+strColOriMS+"],(85+(["+strColNorMS+"]-0.5)*30))<70,0, IF(["+strColUseMS+"]="+strQrtMark+"N"+strQrtMark+",["+strColOriMS+"],(85+(["+strColNorMS+"]-0.5)*30)))";
//objCAL[strColFnlTS] = "=IF(IF(["+strColUseTS+"]="+strQrtMark+"N"+strQrtMark+",["+strColOriTS+"],(85+(["+strColNorTS+"]-0.5)*30))<70,0, IF(["+strColUseTS+"]="+strQrtMark+"N"+strQrtMark+",["+strColOriTS+"],(85+(["+strColNorTS+"]-0.5)*30)))";
//objCAL[strColFnlMS] = "=IF(["+strColStdMS+"]=0, 85, IF(["+strColUseMS+"]="+strQrtMark+"N"+strQrtMark+",["+strColOriMS+"],(85+(["+strColNorMS+"]-0.5)*30)))";
//objCAL[strColFnlTS] = "=IF(["+strColStdTS+"]=0, 85, IF(["+strColUseTS+"]="+strQrtMark+"N"+strQrtMark+",["+strColOriTS+"],(85+(["+strColNorTS+"]-0.5)*30)))";
//objCAL[strColFnlMS] = "=IF(["+strColUseMS+"]="+strQrtMark+"N"+strQrtMark+",["+strColOriMS+"], IF(["+strColStdMS+"]=0, 85, (85+(["+strColNorMS+"]-0.5)*30)))";
//objCAL[strColFnlTS] = "=IF(["+strColUseTS+"]="+strQrtMark+"N"+strQrtMark+",["+strColOriTS+"], IF(["+strColStdTS+"]=0, 85, (85+(["+strColNorTS+"]-0.5)*30)))";
// 누적확률을 돌리지 않으면 원시점수 , 누적확률이 0 이면 기본점수 80 그렇지 않으면 (85+([누적확률]-0.5)*30)
objCAL[strColFnlTS] = "=IF([" + strColUseTS + "]=" + sQM + "N" + sQM + ",[" + strColOriTS + "], IF([" + strColStdTS + "]=0, 85, (85+([" + strColNorTS + "]-0.5)*30)))";
objCAL.SetDirty();
strNorTS = objCAL[strColNorTS].ToString();
strFnlTS = objCAL[strColFnlTS].ToString();
strNorTS = PageUtility.IsAllNumber(strNorTS) ? strNorTS : "0";
strFnlTS = PageUtility.IsAllNumber(strFnlTS) ? strFnlTS : "0";
intRtn = objBSC.UpdateExternalScore
(intEsttermRefID
, intKpiRefID
, strYMD
, strUseTS
, strOriTS
, strAvgTS
, strStdTS
, strAdaTS
, strAdsTS
, strNorTS
, strFnlTS
, itxr_user
);
}
// 최종점수 롤업
MicroBSC.BSC.Biz.Biz_Bsc_Term_Detail objTrm = new MicroBSC.BSC.Biz.Biz_Bsc_Term_Detail();
intRtn = objTrm.SetNormdisScoreRollUp(this.IEstTermRefID, this.IYMD);
}
