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