using Newtonsoft.Json.Linq; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text.RegularExpressions; namespace REPORT_API_ver6.Services { public class Report { static readonly log4net.ILog logger = log4net.LogManager.GetLogger(typeof(Report)); /// /// 특수 옵션 구조 /// public class ReportOptions { public string mode { get; set; } = string.Empty; public string startDate { get; set; } = string.Empty; public string endDate { get; set; } = string.Empty; public string isQuota { get; set; } = string.Empty; public string isTest { get; set; } = string.Empty; public string isTurn { get; set; } = string.Empty; public int prtTotalCase { get; set; } = 0; public int prtTotalPercent { get; set; } = 0; public int prtCase { get; set; } = 0; public int prtPercent { get; set; } = 0; public int roundPointer { get; set; } = 0; public int mRoundPointer { get; set; } = 0; public string prtHeader { get; set; } = string.Empty; public string prtSubTotal { get; set; } = string.Empty; public string prtIndexValue { get; set; } = string.Empty; public string prtNumericSum { get; set; } = string.Empty; public JObject valueGroup { get; set; } = new JObject(); } /// /// /// /// 프로젝트 Key /// 1:키위 프로젝트, 0:KON 프로젝트 /// Index QID들 /// Banner QID들 - '|':Multi Layer, ',':Single Layer /// Filter 쿼리문 /// 특수 옵션 /// public string getResult(string pkey, int iskiwi, string indexString, string bannerString, string filterString, ReportOptions reportOptions) { string returnString = string.Empty; Project project = new Project(); DataTable resultTable = new DataTable(); DataTable totalTable = new DataTable(); DataTable caseTable = new DataTable(); DataTable bannerLabelTable = new DataTable(); string[] numericLable = new string[] { "Median", "SD", "Max", "Min", "Mode" }; if(reportOptions.prtNumericSum == "1") { numericLable = numericLable.Append("Sum").ToArray(); } try { #region data 정리 string projectData = ""; string selectQID = project.makeSelectQID(pkey, indexString, bannerString, iskiwi); if (String.IsNullOrWhiteSpace(reportOptions.mode) == false) { selectQID = selectQID.Replace("''", "'INDEX'"); } projectData = project.getProjectDataJson(pkey, filterString, selectQID, reportOptions.startDate, reportOptions.endDate, iskiwi, reportOptions.isTest, string.Empty); #endregion //if (projectData != "[]" && (String.IsNullOrEmpty(indexString) == false || String.IsNullOrEmpty(indexString) && String.IsNullOrWhiteSpace(reportOptions.isQuota) == false) || reportOptions.isQuota == "1") if (String.IsNullOrEmpty(indexString) == false || String.IsNullOrWhiteSpace(reportOptions.isQuota) == false || String.IsNullOrWhiteSpace(reportOptions.mode) == false) { DataTable questionInfo = project.getQuestionList(pkey, "", iskiwi, "0"); JArray dataJson = Common.GetJArrayFromString(projectData); DataTable indexTable = new DataTable(); Common.tableAddColumn(indexTable, new string[] { "order", "qid", "qtitle", "v_type", "t_type", "q_type", "is_nps", "is_reverse", "missing", "label_info", "rank_count" }); string[][] indexValueArray = setVariableValue(indexTable, questionInfo, indexString, "i", dataJson, reportOptions.mode); if (indexTable.Rows.Count > 0) { int indexCount = indexValueArray[0].Length; JArray[] indexDataArray = new JArray[indexCount]; JArray totalData = new JArray(); Common.tableAddColumn(totalTable, new string[] { "t_value", "i_total" }); Common.tableAddColumn(caseTable, new string[] { "i_value", "i_case" }); Common.tableAddColumn(resultTable, new string[] { "i_value", "i_C", "i_R" }); Common.tableAddColumn(bannerLabelTable, new string[] { "b_value", "b_total" }); //total totalTable.Rows.Add(totalTable.NewRow()); totalTable.Rows[0]["i_total"] = "0"; //missingCount totalTable.Rows.Add(totalTable.NewRow()); totalTable.Rows[1]["i_total"] = "0"; //BannerByBanner Total totalTable.Rows.Add(totalTable.NewRow()); totalTable.Rows[2]["i_total"] = "0"; resultTable.Rows.Add(resultTable.NewRow()); if (String.IsNullOrWhiteSpace(reportOptions.isQuota) == false) { resultTable.Rows[0]["i_value"] = "Total"; } else { resultTable.Rows[0]["i_value"] = "사례수"; } #region index 정리 string indexType = indexTable.Rows[0]["q_type"].ToString(); string indexLevel = indexTable.Rows[0]["v_type"].ToString(); string indexAttrValue = string.Empty; string indexAttr = string.Empty; double[] missingValue = Common.ConvertDoubleArray(indexTable.Rows[0]["missing"].ToString().Split(',')); bool isNPS = false; bool isMean = false; bool isReverse = false; bool isNumeric = false; int rankLoop = 0; JObject attrInfo = new JObject(); double[][] scaleGroup = new double[3][]; // top mid bottom //indexType : single, multi, rank, open => num / text if (indexType == "scale") { if (indexTable.Rows[0]["is_nps"].ToString() == "1") { isNPS = true; } else { isMean = true; } if (indexTable.Rows[0]["is_reverse"].ToString() == "1") { isReverse = true; } scaleGroup = setScaleTMB(indexValueArray[0], isNPS, isReverse, missingValue); } else if (indexType == "rank") { string[] indexStringArray = indexString.Split('_'); indexString = indexStringArray[0]; rankLoop = Convert.ToInt32(indexTable.Rows[0]["rank_count"]); } else if (indexType == "num") { isNumeric = true; } else if (indexType == "text") { NLP nlp = new NLP(); //string wordTagJson = nlp.getWordTagOld(pkey, indexString, "KO", dataJson); string wordTagJson = nlp.getWordTag(pkey, indexString, dataJson); //{ Text = "Text", Count = "Count" } totalTable.Dispose(); caseTable.Dispose(); bannerLabelTable.Dispose(); resultTable.Dispose(); return wordTagJson; } else if (indexType == "come") { return "com 타입은 미구현"; } if (indexLevel == "attr") { if (indexString.IndexOf("_") < 0 || isNumeric && indexString.Split('_').Length < 3) { /* DataRow attrRow = (from DataRow dr in questionInfo.Rows where dr.Field("QID").ToUpper() == indexString select dr).ToList()[0]; */ DataRow attrRow = questionInfo.AsEnumerable().Where(dr => dr.Field("QID").ToUpper() == indexString).ToList()[0]; attrInfo = Common.GetJObjectFromString(attrRow["ATTR_INFO"].ToString()); indexAttr = indexString + "#A|"; } else { string[] indexStringArray = indexString.Split('_'); indexString = indexStringArray[0]; indexAttrValue = indexStringArray[indexStringArray.Length - 1]; if (indexType == "num" || indexType == "text") { indexString += "_" + indexStringArray[1]; } } } #region index total data 정리 totalData = getJarrayCaseData(dataJson, indexString, "key", indexAttrValue, "", "", attrInfo, 0); totalTable.Rows[0]["i_total"] = totalData.Count; ////가중치 적용(INDEX) #endregion #region index case data 정리 double indexSum = 0.0; int missingCount = 0; double maxValue = 0.0; if(indexValueArray[0].Length > 0) { maxValue = Common.ConvertDoubleArray(indexValueArray[0]).Except(missingValue).Max(); } for (int ix = 0; ix < indexCount; ix++) { string multi_value = string.Empty; string index_value = indexValueArray[0][ix].ToString(); if (indexType == "multi") { multi_value = index_value; } JArray IndexData = getJarrayCaseData(dataJson, indexString, "", indexAttrValue, multi_value, index_value, attrInfo, rankLoop); indexDataArray[ix] = IndexData; DataRow resultRow = resultTable.NewRow(); resultRow["i_value"] = index_value; resultTable.Rows.Add(resultRow); DataRow caseRow = caseTable.NewRow(); caseRow["i_value"] = index_value; caseRow["i_case"] = IndexData.Count; ////가중치 적용(INDEX) caseTable.Rows.Add(caseRow); if (isMean || isNumeric) { if (Array.IndexOf(missingValue, Convert.ToDouble(index_value)) < 0) { double indexValue = Convert.ToDouble(index_value); if (isReverse) { indexValue = maxValue - indexValue + 1; } indexSum += indexValue * IndexData.Count; } else { missingCount += IndexData.Count; } } } totalTable.Rows[1]["i_total"] = missingCount; if (isMean || isNumeric) { var meanRow = caseTable.NewRow(); meanRow["i_value"] = "Mean"; meanRow["i_case"] = indexSum; caseTable.Rows.Add(meanRow); var resultRow = resultTable.NewRow(); resultRow["i_value"] = "Mean"; resultTable.Rows.Add(resultRow); if (isNumeric) { List numericList = new List(); List keyList = totalData.Children().Properties().Select(x => x.Name).Distinct().Where(x => x.StartsWith(indexString + "_") || x == indexString).ToList(); for (int ki = 0; ki < keyList.Count; ki++) { numericList.AddRange(totalData.Where(x => x.ToObject>().Keys.Any(k => k == keyList[ki]) && Common.IsNumeric(x.ToObject>()[keyList[ki]])).Select(x => double.Parse(x.ToObject>()[keyList[ki]])).ToList()); } //Median, standard deviation(SD), Max, Min, Mode for (int ni = 0; ni < numericLable.Length; ni++) { var numericCase = caseTable.NewRow(); numericCase["i_value"] = numericLable[ni]; numericCase["i_case"] = getNumericValue(numericList, numericLable[ni]); caseTable.Rows.Add(numericCase); var numericResult = resultTable.NewRow(); numericResult["i_value"] = numericLable[ni]; resultTable.Rows.Add(numericResult); } } } #endregion #endregion #region banner List bannerJarrayList = new List(); string[] bannerArray = bannerString.Split(','); int bannerLoop = bannerArray.Length; for (int i = 0; i < bannerLoop; i++) { #region banner 정리 int bannerRealCount = 0; string[] bannerDetail = bannerArray[i].Split('|'); string bannerReal = ""; if (String.IsNullOrEmpty(indexAttr) == false) { bannerReal = indexAttr; bannerLoop = 1; } else if (String.IsNullOrEmpty(bannerArray[i]) == false) { for (int j = 0; j < bannerDetail.Length; j++) { bannerReal += bannerDetail[j] + "|"; //List bannerList = (from DataRow dr in questionInfo.Rows where dr.Field("QID").ToUpper() == bannerDetail[j] select dr).ToList(); List bannerList = questionInfo.AsEnumerable().Where(dr => dr.Field("QID").ToUpper() == bannerDetail[j]).ToList(); if (bannerList.Count > 0) { var bannerRow = bannerList[0]; string bannerQtype = bannerRow["QTYPE"].ToString(); if (bannerQtype == "text" || bannerQtype == "num") { return "Text, Numeric은 배너로 올릴 수 없습니다."; } else if (bannerQtype == "com") { return "com 타입은 미구현"; } } else { return "배너로 설정된 변수중 없는 변수가 있습니다."; } } } #endregion if (String.IsNullOrEmpty(bannerReal) == false) { bannerReal = bannerReal.Remove(bannerReal.Length - 1, 1); string[] bannerRealArray = bannerReal.Split('|'); bannerRealCount = bannerRealArray.Length; if (bannerRealCount > 5) { return "배너 갯수가 너무 많습니다."; } DataTable bannerTable = new DataTable(); Common.tableAddColumn(bannerTable, new string[] { "order", "qid", "qtitle", "v_type", "t_type", "q_type", "is_nps", "is_reverse", "missing", "label_info", "rank_count" }); string[][] bannerValueArray = setVariableValue(bannerTable, questionInfo, bannerReal, "b", dataJson); for (int bi = bannerLabelTable.Rows.Count; bi < (bannerTable.Rows.Count * 2); bi++) { bannerLabelTable.Rows.Add(bannerLabelTable.NewRow()); } JArray[] bannerTotalData = new JArray[bannerRealCount + 1]; JArray[][] bannerIndexData = new JArray[bannerRealCount + 1][]; if (bannerRealArray[0].IndexOf("#A") < 0) { bannerTotalData[0] = totalData; bannerIndexData[0] = indexDataArray; } else { bannerTotalData[0] = dataJson; JArray[] summaryValue = new JArray[indexCount]; for (int j = 0; j < summaryValue.Length; j++) { summaryValue[j] = dataJson; } bannerIndexData[0] = summaryValue; } for (int j = 1; j < (bannerRealCount + 1); j++) { bannerIndexData[j] = new JArray[indexCount]; } string[] columnArray = new string[bannerValueArray.Length]; /* for (int b0 = 0; b0 < bannerValueArray[0].Length; b0++) { columnArray[0] = setBanner("b" + i, indexString, ref bannerJarrayList, ref bannerTotalData, ref bannerIndexData, ref totalTable, ref caseTable, ref resultTable, ref bannerLabelTable, bannerTable.Rows[0], bannerValueArray, 0, b0, indexValueArray[0], numericLable, isNumeric, isMean, isReverse, missingValue); for (int b1 = 0; b1 < bannerValueArray[1].Length; b1++) { columnArray[1] = setBanner(columnArray[0], indexString, ref bannerJarrayList, ref bannerTotalData, ref bannerIndexData, ref totalTable, ref caseTable, ref resultTable, ref bannerLabelTable, bannerTable.Rows[1], bannerValueArray, 1, b1, indexValueArray[0], numericLable, isNumeric, isMean, isReverse, missingValue); for (int b2 = 0; b2 < bannerValueArray[2].Length; b2++) { columnArray[2] = setBanner(columnArray[1], indexString, ref bannerJarrayList, ref bannerTotalData, ref bannerIndexData, ref totalTable, ref caseTable, ref resultTable, ref bannerLabelTable, bannerTable.Rows[2], bannerValueArray, 2, b2, indexValueArray[0], numericLable, isNumeric, isMean, isReverse, missingValue); for (int b3 = 0; b3 < bannerValueArray[3].Length; b3++) { columnArray[3] = setBanner(columnArray[2], indexString, ref bannerJarrayList, ref bannerTotalData, ref bannerIndexData, ref totalTable, ref caseTable, ref resultTable, ref bannerLabelTable, bannerTable.Rows[3], bannerValueArray, 3, b3, indexValueArray[0], numericLable, isNumeric, isMean, isReverse, missingValue); for (int b4 = 0; b4 < bannerValueArray[4].Length; b4++) { columnArray[4] = setBanner(columnArray[3], indexString, ref bannerJarrayList, ref bannerTotalData, ref bannerIndexData, ref totalTable, ref caseTable, ref resultTable, ref bannerLabelTable, bannerTable.Rows[4], bannerValueArray, 4, b4, indexValueArray[0], numericLable, isNumeric, isMean, isReverse, missingValue); } } } } } */ //재귀 호출시 CPU 사용량 증가로 인해 사용하지 않음 loopBanner(columnArray, "b" + i, bannerValueArray, 0, indexString, ref bannerJarrayList, ref bannerTotalData, ref bannerIndexData, ref totalTable, ref caseTable, ref resultTable, ref bannerLabelTable, bannerTable, indexValueArray[0], numericLable, isNumeric, isMean, isReverse, missingValue); #region 서브토탈 제거 if (reportOptions.prtSubTotal == "0") { List deleteColumn = new List(); for (int bi = 0; bi < bannerLabelTable.Rows.Count - 1; bi++) { for (int bj = 0; bj < bannerLabelTable.Columns.Count - 1; bj++) { if (bannerLabelTable.Rows[bi][bj].ToString() == "Sub Total") { for (int bk = bi - 1; bk >= 0; bk--) { if (String.IsNullOrWhiteSpace(bannerLabelTable.Rows[bk][bj].ToString()) == false) { bannerLabelTable.Rows[bk][bj + 1] = bannerLabelTable.Rows[bk][bj]; } } deleteColumn.Add(bannerLabelTable.Columns[bj].ColumnName); } } } if (deleteColumn.Count > 0) { for (int ci = 0; ci < deleteColumn.Count; ci++) { deleteBannerColumns(totalTable, caseTable, resultTable, bannerLabelTable, bannerJarrayList, deleteColumn[ci]); } } } #endregion } } #endregion #region banner by banner if (reportOptions.mode == "TABULATION_1") { resultTable.Rows.RemoveAt(1); int bi = 2; foreach (JArray bannerOuter in bannerJarrayList) { var caseRow = caseTable.NewRow().ItemArray; var resultRow = resultTable.NewRow().ItemArray; caseRow[0] = caseTable.Columns[bi].ColumnName; caseRow[1] = caseTable.Rows[0][bi].ToString(); resultRow[0] = caseTable.Columns[bi].ColumnName; resultTable.Rows.Add(resultRow); int bj = 2; foreach (JArray bannerInner in bannerJarrayList) { List idkeyList = bannerInner.Select(x => x.Value("IDKEY")).ToList(); int listCount = 0; foreach (string idkey in idkeyList) { //////중복 카운트로 개수를 셀수 있음 listCount += bannerOuter.Where(x => x.Value("IDKEY") == idkey).ToList().Count(); } caseRow[bj] = listCount; bj++; } caseTable.Rows.Add(caseRow); bi++; } caseTable.Rows.RemoveAt(0); string[] columnNameArray = Common.getColumnNames(totalTable); for (int ci = 2; ci < totalTable.Columns.Count; ci++) { if (String.IsNullOrEmpty(totalTable.Rows[2][ci].ToString())) { string columnName = totalTable.Columns[ci].ColumnName; Regex bannerNameRegex = new Regex(columnName.Substring(0, columnName.LastIndexOf("_") + 1) + @"\d+$");//같은 배너만 고름 //List bannerList = (from string cname in columnNameArray where bannerNameRegex.IsMatch(cname) select cname).ToList(); List bannerList = columnNameArray.Where(cname => bannerNameRegex.IsMatch(cname)).ToList(); JArray tempJArray = new JArray(); foreach (string name in bannerList) { tempJArray = Common.mergeJarray(tempJArray, bannerJarrayList[Array.IndexOf(columnNameArray, name) - 2]); } int totalCnt = tempJArray.Select(x => x.Value("IDKEY")).Distinct().Count(); foreach (string name in bannerList) { totalTable.Rows[2][Array.IndexOf(columnNameArray, name)] = totalCnt; } } } } #endregion #region compute resultTable = computeResult(resultTable, totalTable, caseTable, indexType, indexValueArray, scaleGroup, numericLable, reportOptions, isNPS); totalTable.Dispose(); caseTable.Dispose(); #endregion if (indexType == "scale" && isMean) { Common.ReorderDataTableRow(ref resultTable, "i_value ='mean'", -1); } if (String.IsNullOrWhiteSpace(reportOptions.isQuota) == false) { DataTable quotaTable = project.getQuota(pkey, iskiwi, reportOptions.isQuota); resultTable = computeQuota(resultTable, quotaTable, indexString, bannerArray[0].Split('|')); for (int i = bannerLabelTable.Rows.Count - 2; i >= 0; i = i - 2) { bannerLabelTable.Rows.Remove(bannerLabelTable.Rows[i]); } } #region result table label if (indexType != "num" && indexType != "text" && reportOptions.prtIndexValue != "1") { resultTable = setLabel(resultTable, indexTable); } #endregion #region result table header if (reportOptions.prtHeader == "GPT") { resultTable = setGPTHeader(resultTable, bannerLabelTable, reportOptions.prtCase, reportOptions.prtPercent); } else if (reportOptions.prtHeader != "0") { resultTable = setHeader(resultTable, bannerLabelTable, reportOptions.prtCase, reportOptions.prtPercent); } #endregion #region banner by banner 좌측에 헤더 붙이기 if (reportOptions.mode == "TABULATION_1") { string[] labelCellArray = resultTable.Rows[0][0].ToString().Split('|'); labelCellArray[2] = labelCellArray[1]; int rowCount = Convert.ToInt32(Common.GetNullToZero(labelCellArray[1])); resultTable.Columns.RemoveAt(0); List labelList = new List(); for (int i = 0; i < rowCount; i++) { for (int j = 0; j < resultTable.Columns.Count; j++) { string[] cellArray = resultTable.Rows[i][j].ToString().Split('|'); if (i == 0) { string[] labelArray = new string[rowCount]; labelList.Add(labelArray); } if (cellArray.Length > 2) { labelList[j][i] = cellArray[0] + "|" + cellArray[2] + "|" + cellArray[1]; } else { labelList[j][i] = cellArray[0]; } } } for (int i = rowCount - 1; i >= 0; i--) { DataColumn labelColumn = resultTable.Columns.Add("Label_" + i); labelColumn.SetOrdinal(0); for (int j = 0; j < labelList.Count; j++) { resultTable.Rows[j + Convert.ToInt32(labelCellArray[1])][0] = labelList[j][i];/////여기 수정해야함 } } resultTable.Rows[0][0] = String.Join('|', labelCellArray); //resultTable } #endregion #region Quota Total Column //Quota는 Total Column을 끝으로 if (String.IsNullOrWhiteSpace(reportOptions.isQuota) == false) { resultTable.Columns["i_C"].SetOrdinal(resultTable.Columns.Count - 1); } #endregion #region turn table if (reportOptions.isTurn == "1") { DataTable turnTable = new DataTable(); string[] turnRow = new string[resultTable.Rows.Count]; for (int tj = 0; tj < resultTable.Rows.Count; tj++) { turnRow[tj] = "turn_" + (tj + 1); } Common.tableAddColumn(turnTable, turnRow); for (int ti = 0; ti < resultTable.Columns.Count; ti++) { turnRow = new string[turnTable.Columns.Count]; for (int tj = 0; tj < resultTable.Rows.Count; tj++) { string cellValue = resultTable.Rows[tj][ti].ToString(); if (cellValue.IndexOf("|") > -1) { string[] splitArray = cellValue.Split('|'); string tempValue = splitArray[1]; splitArray[1] = splitArray[2]; splitArray[2] = tempValue; cellValue = String.Join("|", splitArray); } turnRow[tj] = cellValue; } DataRow addRow = turnTable.NewRow(); addRow.ItemArray = turnRow; turnTable.Rows.Add(addRow); } resultTable = turnTable; turnTable.Dispose(); } #endregion } } else if (String.IsNullOrEmpty(indexString)) { return "INDEX가 비었습니다."; } else if (String.IsNullOrEmpty(filterString) == false) { return "FILTER 조건이 옳바르지 않거나 해당하는 데이터가 없습니다."; } else if (projectData == "[]") { return "데이터가 없습니다."; } returnString = Common.GetJsonFromObject(resultTable); bannerLabelTable.Dispose(); resultTable.Dispose(); } catch (Exception ex) { logger.Fatal(this.GetType().Name + " > " + System.Reflection.MethodBase.GetCurrentMethod().Name + " : " + ex.Message); return ex.Message.ToString(); } return returnString; } /// /// 문항의 상세정보 정리 및 Label정보 정리 후 반환 /// /// 문항 상세 정보 /// 문항 정보 /// QID들 /// i:Index, b:Banner /// JSON형태의 Data /// Label정보 정리 public string[][] setVariableValue(DataTable variableTable, DataTable questionInfo, string questionString, string vType, JArray dataJson, string mode = "") { string[] detailVariableArray = questionString.Split('|'); int setLength = 5; if (vType == "i") { setLength = detailVariableArray.Length; } string[][] valueArray = new string[setLength][]; if (String.IsNullOrEmpty(questionString)) { string labelString = "전체"; if (mode == "TABULATION_0") { labelString = "%"; } DataRow variableRow = variableTable.NewRow(); variableRow["order"] = "0"; variableRow["qid"] = ""; variableRow["qtitle"] = "전체"; variableRow["v_type"] = "normal"; variableRow["t_type"] = "normal"; variableRow["q_type"] = "single"; variableRow["is_nps"] = "0"; variableRow["is_reverse"] = "0"; variableRow["missing"] = ""; variableRow["label_info"] = "{\"1\":{\"etc\":\"0\",\"except\":\"0\",\"label\":\""+ labelString + "\",\"value\":\"0\"}}"; variableRow["rank_count"] = "0"; variableTable.Rows.Add(variableRow); valueArray[0] = new string[] { "0" }; } else { JObject[] labelArray = new JObject[detailVariableArray.Length]; try { for (int i = 0; i < detailVariableArray.Length; i++) { List screenList = new List(); string labelString = string.Empty; string qID = detailVariableArray[i]; string tType = "normal"; string rankCount = "0"; string infoTarget = "LABEL_INFO"; if (qID.IndexOf("#A") > -1) { qID = qID.Replace("#A", ""); tType = "attr"; infoTarget = "ATTR_INFO"; } /* DataRow variableInfo = (from DataRow dr in questionInfo.Rows where dr.Field("QID").ToUpper() == qID select dr).ToList()[0]; */ DataRow variableInfo = questionInfo.AsEnumerable().Where(dr => dr.Field("QID").ToUpper() == qID).ToList()[0]; if (String.IsNullOrEmpty(variableInfo["RANK_INFO"].ToString()) == false) { rankCount = Common.GetJObjectFromString(variableInfo["RANK_INFO"].ToString())["val"].ToString(); } if (String.IsNullOrEmpty(variableInfo["AFTER_LOGIC"].ToString()) == false) { string logicString = variableInfo["AFTER_LOGIC"].ToString(); JArray logicArray = Common.GetJArrayFromString(logicString); for (int j = 0; j < logicArray.Count; j++) { if (logicArray[j]["condition"].ToString().ToLower().IndexOf("and") < 0) { JArray conditionArray = Common.GetJArrayFromString(logicArray[j]["condition"].ToString()); for (int k = 0; k < conditionArray.Count; k++) { if (logicArray[j]["nextpage"].ToString().ToLower() == "screen" && (conditionArray[k]["va"].ToString().Split('_')[0] == qID || conditionArray[k]["va"].ToString() == qID)) { screenList.Add(new string[] { conditionArray[k]["op"].ToString().Trim(), conditionArray[k]["co"].ToString().Trim() }); } } } } } if ((variableInfo["QTYPE"].ToString() == "num" || variableInfo["QTYPE"].ToString() == "text") && tType != "attr") { labelString += "{ "; List valueList = new List(); List keyList = dataJson.Children().Properties().Select(x => x.Name).Distinct().Where(x => x.StartsWith(qID + "_") || x == qID).ToList(); for (int j = 0; j < keyList.Count; j++) { valueList.AddRange(dataJson.Where(x => x.ToObject>().Keys.Any(k => k == keyList[j])).Select(x => x.ToObject>()[keyList[j]]).Distinct().ToList()); } if (variableInfo["QTYPE"].ToString() == "num") { var numericList = valueList.Where(x => Common.IsNumeric(x)).Select(x => double.Parse(x)).Distinct().ToList(); numericList.Sort(); valueList = numericList.Select(x => x.ToString()).ToList(); } else { valueList.Sort(); } if (valueList.Count > 0) { for (int j = 0; j < valueList.Count; j++) { labelString += "\"" + (j + 1) + "\":{ \"label\":\"" + valueList[j] + "\",\"value\":\"" + valueList[j] + "\"} ,"; } labelString.Remove(labelString.Length - 1, 1); } labelString += " }"; } else { labelString = variableInfo[infoTarget].ToString(); } labelString = labelString.Replace("|", "#"); labelArray[i] = Common.GetJObjectFromString(labelString); List valueDetailList = new List(); for (int j = 0; j < labelArray[i].Count; j++) { string labelValue = labelArray[i].Children().ToArray()[j].Children()["value"].ToArray()[0].ToString(); valueDetailList.Add(labelValue); } for (int j = 0; j < screenList.Count; j++) { if (Common.IsNumeric(screenList[j][1])) { switch (screenList[j][0]) { case "=": valueDetailList = valueDetailList.Where(x => x.ToString() != screenList[j][1]).ToList(); break; case "!=": valueDetailList = valueDetailList.Where(x => x.ToString() == screenList[j][1]).ToList(); break; case "<": valueDetailList = valueDetailList.Where(x => Convert.ToInt32(x) >= Convert.ToInt32(screenList[j][1])).ToList(); break; case ">": valueDetailList = valueDetailList.Where(x => Convert.ToInt32(x) <= Convert.ToInt32(screenList[j][1])).ToList(); break; case "<=": valueDetailList = valueDetailList.Where(x => Convert.ToInt32(x) > Convert.ToInt32(screenList[j][1])).ToList(); break; case ">=": valueDetailList = valueDetailList.Where(x => Convert.ToInt32(x) < Convert.ToInt32(screenList[j][1])).ToList(); break; } } } valueArray[i] = valueDetailList.ToArray(); DataRow variableRow = variableTable.NewRow(); variableRow["order"] = i.ToString(); variableRow["qid"] = qID; variableRow["qtitle"] = variableInfo["TITLE"]; variableRow["v_type"] = variableInfo["VTYPE"]; variableRow["t_type"] = tType; variableRow["q_type"] = variableInfo["QTYPE"]; variableRow["is_nps"] = variableInfo["IS_NPS"]; variableRow["is_reverse"] = variableInfo["IS_REVERSE"]; variableRow["missing"] = variableInfo["MISSING_VALUE"]; variableRow["label_info"] = labelString; variableRow["rank_count"] = rankCount; variableTable.Rows.Add(variableRow); } for (int i = detailVariableArray.Length; i < setLength; i++) { valueArray[i] = new string[0]; } } catch (Exception ex) { logger.Fatal(this.GetType().Name + " > " + System.Reflection.MethodBase.GetCurrentMethod().Name + " : " + ex.Message); } } return valueArray; } /// /// 프로젝트 Data의 특정 케이스를 Jarray형태로 머지 후 반환 /// /// Jarray형태의 DATA /// Kon QID /// Key로 Count할 지 여부 /// Kon 속성 값 /// Kon Multi 값 /// 응답 값 /// 속성 루프 횟수 /// Multi 루프 횟수 /// Jarray형태의 DATA public JArray getJarrayCaseData(JArray jsonData, string qID, string selectType, string attrValue, string multiValue, string qValue, JObject attrInfo, int setMultiLoop) { JArray returnArray = new JArray(); int attrLoop = 1; int multiLoop = 1; if (attrInfo.Count > attrLoop) { attrLoop = attrInfo.Count; } if (setMultiLoop > multiLoop) { multiLoop = setMultiLoop; } for (int attrCount = 0; attrCount < attrLoop; attrCount++) { string attrString = ""; if (attrInfo.Count > 0) { attrString = "_" + attrInfo[(attrCount + 1).ToString()]["value"].ToString(); } else if (String.IsNullOrEmpty(attrValue) == false) { attrString = "_" + attrValue; } for (int multiCount = 0; multiCount < multiLoop; multiCount++) { string multiString = ""; if (setMultiLoop != 0) { multiString = "_" + (multiCount + 1); } else if (String.IsNullOrEmpty(multiValue) == false) { multiString = "_" + multiValue; } if (String.IsNullOrEmpty(qID)) { returnArray = Common.mergeJarray(returnArray, new JArray(jsonData)); } else if (selectType == "key") { if (qID.IndexOf("_") < 0) { returnArray = Common.mergeJarray(returnArray, new JArray(jsonData.Where( x => x is JObject y && y.ToObject>().Keys.Any( k => (k.Split('_')[0] == qID.ToUpper()) && k.EndsWith(multiString + attrString) && y.Value(k) != "" ) ))); } else { returnArray = Common.mergeJarray(returnArray, new JArray(jsonData.Where( x => x is JObject y && y.ToObject>().Keys.Any( k => (k.StartsWith(qID.ToUpper() + "_") || k == qID.ToUpper()) && k.EndsWith(multiString + attrString) && y.Value(k) != "" ) ))); } } else { returnArray = Common.mergeJarray(returnArray, new JArray( jsonData .Where(x => x.Value(qID.ToUpper() + multiString + attrString) != "" && x.Value(qID.ToUpper() + multiString + attrString) != null) .Where(x => x.Value(qID.ToUpper() + multiString + attrString) == Convert.ToDouble(qValue)) )); } } } return returnArray; } //재귀 호출시 CPU 사용량 증가로 인해 사용하지 않음 public void loopBanner(string[] columnArray, string bString, string[][] bannerValueArray, int bLevel, string indexString, ref List bannerJarrayList, ref JArray[] bannerTotalData, ref JArray[][] bannerIndexData, ref DataTable totalTable, ref DataTable caseTable, ref DataTable resultTable, ref DataTable bannerLabelTable, DataTable bannerTable, string[] indexValues, string[] numericLable, bool isNumeric, bool isMean, bool isReverse, double[] missingValue) { if (bLevel < columnArray.Length) { for (int bi = 0; bi < bannerValueArray[bLevel].Length; bi++) { columnArray[0] = setBanner(bString, indexString, ref bannerJarrayList, ref bannerTotalData, ref bannerIndexData, ref totalTable, ref caseTable, ref resultTable, ref bannerLabelTable, bannerTable.Rows[bLevel], bannerValueArray, bLevel, bi, indexValues, numericLable, isNumeric, isMean, isReverse, missingValue); loopBanner(columnArray, columnArray[0], bannerValueArray, bLevel + 1, indexString, ref bannerJarrayList, ref bannerTotalData, ref bannerIndexData, ref totalTable, ref caseTable, ref resultTable, ref bannerLabelTable, bannerTable, indexValues, numericLable, isNumeric, isMean, isReverse, missingValue); } } } /// /// Banner Setting /// /// 상위 Banner Cell의 Column명 /// Index QID /// Total Data /// Index Data /// Total Sample Table /// Case Sample Table /// Result Table /// Banner Label 정리 Table /// Banner 정보 /// Banner 값들 /// 현재 Layer 단계 /// 현재 Banner내에서 번째 수 /// Index 값들 /// Numeric 변수의 Label들 /// 인덱스의 Numeric 여부 /// 인덱스의 Mean 여부 /// Banner Column명 public string setBanner(string bString, string indexString, ref List bannerSource, ref JArray[] totalSource, ref JArray[][] indexSource, ref DataTable totalTable, ref DataTable caseTable, ref DataTable resultTable, ref DataTable bannerLabelTable, DataRow bannerInfo, string[][] bannerValues, int bLevel, int bCurrent, string[] indexValues, string[] numericLable, bool isNumeric, bool isMean, bool isReverse, double[] missingValue) { string columnString = bString + "_" + bannerValues[bLevel][bCurrent].ToString(); addBannerColumns(totalTable, caseTable, resultTable, bannerLabelTable, columnString); getBannerJarray(columnString, totalTable, caseTable, ref bannerSource, ref totalSource, ref indexSource, bannerInfo, bannerValues, bLevel, bCurrent, indexValues, isNumeric, isMean, isReverse, missingValue); if (isNumeric) { setBannerNumeric(columnString, caseTable, indexSource, numericLable, indexString, bLevel); } if (bCurrent == 0) { bannerLabelTable.Rows[bLevel * 2][columnString] = bannerInfo["qtitle"]; if (bLevel > 0) { bannerLabelTable.Rows[bLevel * 2][bString] = "Sub Total"; } } JObject bLabelList = Common.GetJObjectFromString(bannerInfo["label_info"].ToString()); string bLabel = bLabelList.Properties().Where(x => x.Value.Value("value") == bannerValues[bLevel][bCurrent]).Select(x => x.Value.Value("label")).ToList()[0]; if (String.IsNullOrWhiteSpace(bLabel)) { bLabel = bannerValues[bLevel][bCurrent]; } bannerLabelTable.Rows[(bLevel * 2) + 1][columnString] += bLabel; return columnString; } /// /// Banner와 관련된 Table에 컬럼추가 /// /// Total Sample Table /// Case Sample Table /// Result Table /// Banner Label Table /// 컬럼명 public void addBannerColumns(DataTable totalTable, DataTable caseTable, DataTable resultTable, DataTable bannerLabelTable, string coloumnString) { Common.tableAddColumn(totalTable, new string[] { coloumnString }); Common.tableAddColumn(caseTable, new string[] { coloumnString }); Common.tableAddColumn(resultTable, new string[] { coloumnString + "_C" }); Common.tableAddColumn(resultTable, new string[] { coloumnString + "_R" }); Common.tableAddColumn(bannerLabelTable, new string[] { coloumnString }); } /// /// Banner와 관련된 Table에 컬럼제거 /// /// Total Sample Table /// Case Sample Table /// Result Table /// Banner Label Table /// 컬럼명 public void deleteBannerColumns(DataTable totalTable, DataTable caseTable, DataTable resultTable, DataTable bannerLabelTable, List bannerSource, string coloumnString) { int indexNum = totalTable.Columns.IndexOf(totalTable.Columns[coloumnString]); totalTable.Columns.Remove(coloumnString); caseTable.Columns.Remove(coloumnString); resultTable.Columns.Remove(coloumnString + "_C"); resultTable.Columns.Remove(coloumnString + "_R"); bannerLabelTable.Columns.Remove(coloumnString); bannerSource.RemoveAt(indexNum - 2); } /// /// Banner에 맞는 DATA를 JArray로 정리 /// /// /// Total Sample Table /// Case Sample Table /// Total Data /// Index Data /// Banner 정보 /// Banner 값들 /// 현재 Layer 단계 /// 현재 Banner내에서 번째 수 /// Index 값들 /// 인덱스의 Numeric 여부 /// 인덱스의 Mean 여부 public void getBannerJarray(string coloumnString, DataTable totalTable, DataTable caseTable, ref List bannerSource, ref JArray[] totalSource, ref JArray[][] indexSource, DataRow bannerInfo, string[][] bannerValues, int bLevel, int bCurrent, string[] indexValues, bool isNumeric, bool isMean, bool isReverse, double[] missingValue) { //data 처리 //total //loop 필요 없음, attr value 필요, multi vlaue는 1로 고정 string bannerQid = bannerInfo["qid"].ToString(); string bannerValue = bannerValues[bLevel][bCurrent]; string[] bValues = new string[3] { "", "", "" }; // 0 : attr value, 1 : multi value, 2: q_value string getTotalType = ""; string getCaseType = ""; string bannerQType = bannerInfo["q_type"].ToString().ToLower(); int b0MultiLoop = 0; int b0MultiLoopTotal = 0; bValues[2] = bannerValue; if (bannerInfo["t_type"].ToString().ToLower() == "attr") { getTotalType = "key"; bValues[0] = bannerValue; } else if (bannerInfo["v_type"].ToString().ToLower() == "attr") { if (bannerQid.IndexOf("_") > -1) { string[] bannerQidArray = bannerQid.Split('_'); bannerQid = bannerQidArray[0]; bValues[0] = bannerQidArray[bannerQidArray.Length - 1]; } } if (bannerQType == "rank") { string[] bannerQidArray = bannerQid.Split('_'); bannerQid = bannerQidArray[0]; b0MultiLoop = Convert.ToInt32(bannerInfo["rank_count"]); b0MultiLoopTotal = Convert.ToInt32(bannerInfo["rank_count"]); } else if (bannerQType == "multi") { getTotalType = "key"; if (bannerInfo["t_type"].ToString().ToLower() == "attr") { b0MultiLoop = indexValues.Length; } else { bValues[1] = bannerValue; } } JArray sourceJArray = getJarrayCaseData(totalSource[bLevel], bannerQid, getTotalType, bValues[0], bValues[1], bValues[2], new JObject(), b0MultiLoopTotal); totalSource[bLevel + 1] = sourceJArray; bannerSource.Add(sourceJArray); totalTable.Rows[0][coloumnString] = totalSource[bLevel + 1].Count; ////가중치 적용(BANNER) //case //attr loop는 필요 없음 //attr의 multi면 multi loop는 필요 //어떤 경우든 rank는 multi loop 필요 double bannerSum = 0.0; int missingCount = 0; double maxValue = 0.0; if(indexValues.Length > 0) { maxValue = Common.ConvertDoubleArray(indexValues).Except(missingValue).Max(); } for (int ix = 0; ix < indexValues.Length; ix++) { if (bannerInfo["t_type"].ToString().ToLower() == "attr") { bValues[2] = indexValues[ix]; } indexSource[bLevel + 1][ix] = getJarrayCaseData(indexSource[bLevel][ix], bannerQid, getCaseType, bValues[0], bValues[1], bValues[2], new JObject(), b0MultiLoop); caseTable.Rows[ix][coloumnString] = indexSource[bLevel + 1][ix].Count; ////가중치 적용(BANNER) if (isMean || isNumeric) { if (Array.IndexOf(missingValue, Convert.ToDouble(indexValues[ix])) < 0) { double indexValue = Convert.ToDouble(indexValues[ix]); if (isReverse) { indexValue = maxValue - indexValue + 1; } bannerSum += indexValue * indexSource[bLevel + 1][ix].Count; ////가중치 적용(BANNER) } else { missingCount += indexSource[bLevel + 1][ix].Count; } } } if (isMean || isNumeric) { caseTable.Rows[indexValues.Length][coloumnString] = bannerSum; } totalTable.Rows[1][coloumnString] = missingCount; ////가중치 적용(BANNER) } /// /// Index가 Numeric의 경우 Banner값 세팅 /// /// 컬럼명 /// Case Sample Table /// Index Data /// Numeric 변수의 Label들 /// Index QID /// 현재 Layer 단계 public void setBannerNumeric(string coloumnString, DataTable caseTable, JArray[][] indexSource, string[] numericLable, string indexString, int bLevel) { JArray numericJarray = new JArray(); int loopCount = indexSource[bLevel + 1].Length; for (int i = 0; i < loopCount; i++) { numericJarray = Common.mergeJarray(numericJarray, indexSource[bLevel + 1][i]); } List numericList = new List(); List keyList = numericJarray.Children().Properties().Select(x => x.Name).Distinct().Where(x => x.StartsWith(indexString + "_") || x == indexString).ToList(); for (int ki = 0; ki < keyList.Count; ki++) { numericList.AddRange(numericJarray.Where(x => x.ToObject>().Keys.Any(k => k == keyList[ki]) && Common.IsNumeric(x.ToObject>()[keyList[ki]])).Select(x => double.Parse(x.ToObject>()[keyList[ki]])).ToList()); } for (int ni = 0; ni < numericLable.Length; ni++) { int ci = loopCount + 1 + ni; caseTable.Rows[ci][coloumnString] = getNumericValue(numericList, numericLable[ni]); ////가중치 적용(BANNER) } } /// /// 결과 값 계산 /// /// Result Table /// Total Sample Table /// Case Sample Table /// Index 변수 타입 /// Index 값 Array /// Top, Mid, Bottom /// Numeric 변수의 Label들 /// 특수 옵션 /// NPS 변수 여부 /// Result Table public DataTable computeResult(DataTable resultTable, DataTable totalTable, DataTable caseTable, string indexType, string[][] indexValueArray, double[][] scaleGroup, string[] numericLable, ReportOptions reportOptions, bool isNPS) { if (indexType == "scale") { string[] tmbLabel = new string[] { "Top", "Mid", "Bottom", "Missing" }; if (isNPS) { tmbLabel = new string[] { "Promoters", "Passives", "Detractors" }; } for (int si = 0; si < tmbLabel.Length; si++) { if (scaleGroup[si].Length > 0) { string labelString = tmbLabel[si]; if (isNPS == false) { if(labelString == "Missing") { labelString += " (" + String.Join(",", scaleGroup[si]) +")"; } else { labelString += " " + scaleGroup[si].Length; } } DataRow tmbRow = resultTable.NewRow(); tmbRow["i_value"] = labelString; resultTable.Rows.Add(tmbRow); } } ///tmbLabel이랑 scaleGroup이랑 길이가 맞아야 해서 따로 분리 if (isNPS) { DataRow tmbRow = resultTable.NewRow(); tmbRow["i_value"] = "NPS"; resultTable.Rows.Add(tmbRow); } } List keyList = new List(); if (reportOptions.valueGroup != null && reportOptions.valueGroup.Count > 0) { keyList = reportOptions.valueGroup.Properties().Select(p => p.Name).ToList(); for (int vi = 0; vi < keyList.Count; vi++) { if (String.IsNullOrWhiteSpace(reportOptions.valueGroup[keyList[vi]].ToString()) == false) { DataRow tmbRow = resultTable.NewRow(); tmbRow["i_value"] = keyList[vi]; resultTable.Rows.Add(tmbRow); } } } int rColumn = 1; for (int c = 1; c < caseTable.Columns.Count; c++) { double totalValue = Convert.ToDouble(totalTable.Rows[0][c]); double missingCount = Convert.ToDouble(totalTable.Rows[1][c]); double totalPercent = 0; int r = 0; for (; r < caseTable.Rows.Count; r++) { string indexValue = resultTable.Rows[r + 1][0].ToString(); string resultValue = "0"; string caseValue = Common.GetNullToZero(caseTable.Rows[r][c]); if (indexValue == "Mean") { caseValue = Common.RoundValue((Convert.ToDouble(caseValue) / (totalValue - missingCount)).ToString().Replace("NaN", "-"), reportOptions.mRoundPointer); } else if (Array.IndexOf(numericLable, indexValue) > -1) { caseValue = Common.RoundValue(caseValue.Replace("NaN", "-"), reportOptions.roundPointer); } else { double computeTotal = totalValue; if (reportOptions.mode == "TABULATION_0") { string bannerName = caseTable.Columns[c].ColumnName; Regex bannerNameRegex = new Regex(bannerName.Substring(0, bannerName.Length-1) +@"\d$");//같은 배너만 고름 string[] matchNames = Common.getColumnNames(totalTable).Where(x => bannerNameRegex.Matches(x).Count > 0).ToArray(); if(matchNames.Length > 0) { computeTotal = 0; foreach (string matchName in matchNames) { computeTotal+= Convert.ToDouble(totalTable.Rows[0][matchName]); } } else { computeTotal = Convert.ToDouble(totalTable.Rows[0][1]); } } else if(reportOptions.mode == "TABULATION_1") { if (c == 1) { computeTotal = Convert.ToDouble(totalTable.Rows[2][indexValue]); } else { computeTotal = Convert.ToDouble(totalTable.Rows[0][c]); } } resultValue = (Convert.ToDouble(caseValue) * 100 / computeTotal).ToString().Replace("NaN", "0"); totalPercent += Convert.ToDouble(resultValue); } resultTable.Rows[r + 1][rColumn] = caseValue; resultTable.Rows[r + 1][rColumn + 1] = Common.RoundValue(resultValue, reportOptions.roundPointer); if (indexValue == "Mean" || Array.IndexOf(numericLable, indexValue) > -1) { resultTable.Rows[r + 1][rColumn] += "||" + (reportOptions.prtCase + reportOptions.prtPercent); // case // case | rowsapn | colspan resultTable.Rows[r + 1][rColumn + 1] = ""; // percent //prtCase, prtPercent에 따라 다르게 if (reportOptions.prtCase == 0) { resultTable.Rows[r + 1][rColumn + 1] = caseValue; } } } if (reportOptions.prtTotalPercent == 0 && reportOptions.prtTotalCase == 1) { resultTable.Rows[0][rColumn] = totalValue.ToString(); resultTable.Rows[0][rColumn + 1] = totalValue.ToString(); } else if (reportOptions.prtTotalPercent == 1 && reportOptions.prtTotalCase == 0) { resultTable.Rows[0][rColumn] = Common.RoundValue(totalPercent, reportOptions.roundPointer).ToString(); resultTable.Rows[0][rColumn + 1] = Common.RoundValue(totalPercent, reportOptions.roundPointer).ToString(); } else { resultTable.Rows[0][rColumn] = totalValue; resultTable.Rows[0][rColumn + 1] = Common.RoundValue(totalPercent, reportOptions.roundPointer); } if (indexType == "scale") { int promoterPointer = 0; int detractorPointer = 0; for (int si = 0; si < scaleGroup.Length; si++) { if (scaleGroup[si].Length > 0) { if (isNPS) { if (si == 0) { promoterPointer = r + 1; } else if (si == scaleGroup.Length - 2) { detractorPointer = r + 1; } } double tmbCase = 0.0; for (int sj = 0; sj < scaleGroup[si].Length; sj++) { int valuePointer = Array.IndexOf(indexValueArray[0], scaleGroup[si][sj].ToString()); tmbCase += Convert.ToDouble(caseTable.Rows[valuePointer][c]); } resultTable.Rows[r + 1][rColumn] = tmbCase; var test123 = tmbCase * 100 / totalValue; resultTable.Rows[r + 1][rColumn + 1] = Common.RoundValue((tmbCase * 100 / totalValue).ToString().Replace("NaN", "-"), reportOptions.roundPointer); r++; } } if (isNPS) { resultTable.Rows[r + 1][rColumn] = Convert.ToDouble(resultTable.Rows[promoterPointer][rColumn]) - Convert.ToDouble(resultTable.Rows[detractorPointer][rColumn]); string promoterValue = resultTable.Rows[promoterPointer][rColumn + 1].ToString(); string detractorValue = resultTable.Rows[detractorPointer][rColumn + 1].ToString(); if (Common.IsNumeric(promoterValue) && Common.IsNumeric(detractorValue)) { resultTable.Rows[r + 1][rColumn + 1] = Common.RoundValue((Convert.ToDouble(resultTable.Rows[promoterPointer][rColumn + 1]) - Convert.ToDouble(resultTable.Rows[detractorPointer][rColumn + 1])).ToString().Replace("NaN", "-"), reportOptions.roundPointer); } else { resultTable.Rows[r + 1][rColumn + 1] = "-"; } r++; } } for (int vi = 0; vi < keyList.Count; vi++) { if (String.IsNullOrWhiteSpace(reportOptions.valueGroup[keyList[vi]].ToString()) == false) { double tmbCase = 0.0; string[] valueStringArray = reportOptions.valueGroup[keyList[vi]].ToString().Split(','); for (int vj = 0; vj < valueStringArray.Length; vj++) { int valuePointer = Array.IndexOf(indexValueArray[0], valueStringArray[vj]); tmbCase += Convert.ToDouble(caseTable.Rows[valuePointer][c]); } resultTable.Rows[r + 1][rColumn] = tmbCase; resultTable.Rows[r + 1][rColumn + 1] = Common.RoundValue((tmbCase * 100 / totalValue).ToString().Replace("NaN", "-"), reportOptions.roundPointer); r++; } } rColumn += 2;//고정 } if (reportOptions.prtCase + reportOptions.prtPercent < 2) { string endString = "_R"; if (reportOptions.prtCase == 0) { endString = "_C"; } string[] colNames = resultTable.Columns.Cast().Select(x => x.ColumnName).Where(x => x.EndsWith(endString)).ToArray(); for (int i = 0; i < colNames.Length; i++) { resultTable.Columns.Remove(colNames[i]); } } return resultTable; } /// /// 쿼터 결과 계산 /// /// Result Table /// Total Sample Table /// Case Sample Table /// Quota 테이블 /// index 변수명 /// Banner에 쓰인 QID들 /// public DataTable computeQuota(DataTable resultTable, DataTable quotaTable, string indexString, string[] bannerQIDs) { #region set Quota DataTable quotaCountTable = resultTable.Clone(); DataTable isUseTable = resultTable.Clone(); for (int rowi = 0; rowi < resultTable.Rows.Count; rowi++) { int totalCnt = 0; quotaCountTable.Rows.Add(quotaCountTable.NewRow()); isUseTable.Rows.Add(isUseTable.NewRow()); //List rowList = (from DataRow dr in quotaTable.Rows select dr).ToList(); List rowList = quotaTable.AsEnumerable().ToList(); for (int j = 1; j < resultTable.Columns.Count; j++) { string isUse = string.Empty; List quotaList = new List(); if (j == 1)//row값 { string rowValue = resultTable.Rows[rowi][0].ToString(); if (Common.IsNumeric(rowValue) && String.IsNullOrEmpty(indexString) == false) { rowList = rowList.Where(x => Common.GetJObjectFromString(x.Field("QUOTAJSON"))[indexString].ToString() == rowValue).ToList(); } quotaList = rowList; } else { quotaList = rowList; string[] bannerValues = resultTable.Columns[j].ColumnName.Split('_'); for (int k = 1; k < bannerValues.Length - 1; k++) { var bannerQID = bannerQIDs[k - 1]; var bannerValue = bannerValues[k]; quotaList = quotaList.Where(x => Common.GetJObjectFromString(x.Field("QUOTAJSON"))[bannerQIDs[k - 1]].ToString() == bannerValues[k]).ToList(); } } int quotaCount = getQuotaSum(quotaList); totalCnt += quotaCount; if (quotaList.Count == 1) { isUse = "/" + quotaList[0]["IS_USE"].ToString() + "/" + quotaList[0]["id"].ToString(); } quotaCountTable.Rows[rowi][j] = quotaCount; isUseTable.Rows[rowi][j] = isUse; } if (totalCnt == 0) { resultTable.Rows.Remove(resultTable.Rows[rowi]); quotaCountTable.Rows.Remove(quotaCountTable.Rows[rowi]); isUseTable.Rows.Remove(isUseTable.Rows[rowi]); rowi--; } } for (int col = 1; col < resultTable.Columns.Count; col++) { resultTable.Rows[0][col] = resultTable.AsEnumerable().Where(row => row.Field("i_value") != "Total").Sum(row => Convert.ToInt32(row.Field(resultTable.Columns[col].ColumnName))); } for (int rowi = 0; rowi < resultTable.Rows.Count; rowi++) { //List rowList = (from DataRow dr in quotaTable.Rows select dr).ToList(); List rowList = quotaTable.AsEnumerable().ToList(); for (int j = 1; j < resultTable.Columns.Count; j++) { double complete = Convert.ToDouble(resultTable.Rows[rowi][j].ToString()); double quotaCount = Convert.ToDouble(quotaCountTable.Rows[rowi][j].ToString()); string quotaPercent = Common.RoundValue((complete * 100 / quotaCount).ToString().Replace("NaN", "-"), 2); string isUse = isUseTable.Rows[rowi][j].ToString(); resultTable.Rows[rowi][j] = complete + "/" + quotaCount + "/" + quotaPercent + isUse; } } DataRow totalRow = resultTable.Rows[0]; object[] rowItemArray = totalRow.ItemArray; resultTable.Rows.Remove(totalRow); if (String.IsNullOrEmpty(indexString) == false) { DataRow newRow = resultTable.NewRow(); newRow.ItemArray = rowItemArray; resultTable.Rows.InsertAt(newRow, resultTable.Rows.Count); } return resultTable; #endregion } /// /// Result Table에 GPT용 Header Row 추가 /// /// ResultTable /// Banner Label Table /// 전체 N수 출력 여부 /// 전체 Percent 출력 여부 /// N수 출력 여부 /// Percent 출력 여부 /// public DataTable setGPTHeader(DataTable resultTable, DataTable bannerLabelTable, int prtCase, int prtPercent) { int rowAdd = 0; if (prtCase == 1 && prtPercent == 1) { rowAdd = 1; resultTable.Rows.InsertAt(resultTable.NewRow(), 0); for (int c = 1; c < resultTable.Columns.Count; c++) { string header = "N"; if (resultTable.Columns[c].ColumnName.EndsWith("_R")) { header = "%"; } resultTable.Rows[0][c] = header; } } if (bannerLabelTable.Rows.Count > 0) { resultTable.Rows.InsertAt(resultTable.NewRow(), 0); DataTable tempHeaderTable = bannerLabelTable.Clone(); for (int i = 1; i < bannerLabelTable.Rows.Count; i = i + 2) { var tempArray = bannerLabelTable.Rows[i].ItemArray; for (int j = 1; j < tempArray.Length; j++) { if (tempArray[j].ToString() == "") { tempArray[j] = tempArray[j - 1]; } } tempHeaderTable.Rows.Add(tempArray); } for (int j = 1; j < tempHeaderTable.Columns.Count; j++) { string headrString = ""; for (int i = 0; i < tempHeaderTable.Rows.Count; i++) { string headerCell = tempHeaderTable.Rows[i][j].ToString().Trim(); if (headerCell != "") { headrString += " * " + headerCell; } } if(headrString.Length > 3) { headrString = headrString.Substring(3, headrString.Length - 3); } resultTable.Rows[0][(j * (prtCase + prtPercent)) - (prtCase + prtPercent - 1)] = headrString+"||"+(prtCase + prtPercent); } } resultTable.Rows[0][0] = "|" + (1 + rowAdd) + "|"; // | rowspan | colspan resultTable.Rows[0][1] = "Total||" + (prtCase + prtPercent); //Total | rowspan | colspan return resultTable; } /// /// Result Table에 Header Row 추가 /// /// ResultTable /// Banner Label Table /// 전체 N수 출력 여부 /// 전체 Percent 출력 여부 /// N수 출력 여부 /// Percent 출력 여부 /// ResultTable public DataTable setHeader(DataTable resultTable, DataTable bannerLabelTable, int prtCase, int prtPercent) { // label | "r"rowspan | "c"colspan int rowAdd = 0; if (prtCase == 1 && prtPercent == 1) { rowAdd = 1; resultTable.Rows.InsertAt(resultTable.NewRow(), 0); for (int c = 1; c < resultTable.Columns.Count; c++) { string header = "N"; if (resultTable.Columns[c].ColumnName.EndsWith("_R")) { header = "%"; } resultTable.Rows[0][c] = header; } } int rowSpan = bannerLabelTable.Rows.Count; string[] bannerColumnArray = Common.getColumnNames(bannerLabelTable); if (rowSpan > 0) { for (int i = rowSpan - 1; i >= 0; i--) { string bannerPointer = string.Empty; int bannerColSpan = 1; int bannerRowSpan = rowSpan - i; object[] addItem = bannerLabelTable.Rows[i].ItemArray; for (int j = addItem.Length - 1; j >= 0; j--) { if (bannerPointer != bannerColumnArray[j].Split('_')[0]) { bannerColSpan = 1; bannerPointer = bannerColumnArray[j].Split('_')[0]; } if (String.IsNullOrEmpty(addItem[j].ToString()) == false) { string rowString = ""; string colString = ""; if (bannerColSpan == 1 && (prtCase + prtPercent) == 2) { colString = (prtCase + prtPercent).ToString(); } //헤더에서 ColSpan과 RowSpan이 동시에 오면 안됨 if (bannerColSpan > 1) { colString = (bannerColSpan * (prtCase + prtPercent)).ToString(); bannerColSpan = 1; } if (bannerRowSpan > 1 && i != rowSpan - 1 && String.IsNullOrEmpty(bannerLabelTable.Rows[i + 1][j].ToString())) { rowString = bannerRowSpan.ToString(); } addItem[j] += "|" + rowString + "|" + colString; } else { if (i > 0) { bannerColSpan++; for (int row = 0; row < i; row++) { if (bannerLabelTable.Rows[row][j].ToString() == "Sub Total") { bannerColSpan--; } } } else { bannerColSpan++; } } } DataRow addRow = resultTable.NewRow(); string[] labelArray = new string[addRow.ItemArray.Length]; labelArray[0] = string.Empty; for (int c = 1; c < addItem.Length; c++) { labelArray[(c * (prtCase + prtPercent)) - (prtCase + prtPercent - 1)] = addItem[c].ToString(); } addRow.ItemArray = labelArray; resultTable.Rows.InsertAt(addRow, 0); } //resultTable.Rows[0][0] = "Label|" + (rowSpan + rowAdd) + "|"; //Label | rowspan | colspan resultTable.Rows[0][0] = "|" + (rowSpan + rowAdd) + "|"; // | rowspan | colspan resultTable.Rows[0][1] = "Total|" + (rowSpan) + "|" + (prtCase + prtPercent); //Total | rowspan | colspan } else { DataRow addRow = resultTable.NewRow(); resultTable.Rows.InsertAt(addRow, 0); //resultTable.Rows[0][0] = "Label|" + (1 + rowAdd) + "|"; // | rowspan | colspan resultTable.Rows[0][0] = "|" + (1 + rowAdd) + "|"; // | rowspan | colspan resultTable.Rows[0][1] = "Total||" + (prtCase + prtPercent); //Total | rowspan | colspan } return resultTable; } /// /// Label 값 교체 /// /// Result Table /// Index 정보 테이블 /// public DataTable setLabel(DataTable resultTable, DataTable indexTable) { JObject indexLabel = Common.GetJObjectFromString(indexTable.Rows[0]["label_info"].ToString()); for (int i = 0; i < resultTable.Rows.Count; i++) { if (i <= indexLabel.Count) { string iValue = resultTable.Rows[i]["i_value"].ToString(); List iLabelList = indexLabel.Properties().Where(x => x.Value.Value("value") == iValue).Select(x => x.Value.Value("label")).ToList(); if (iLabelList.Count > 0) { string iLabel = iLabelList[0]; if (String.IsNullOrWhiteSpace(iLabel)) { iLabel = iValue; } resultTable.Rows[i]["i_value"] = iLabel; } } } return resultTable; } /// /// Scale변수의 Default Top, Mid, Bottom값 세팅 /// /// Index 값들 /// NPS 여부 /// Top, Mid, Bottom값 Array public double[][] setScaleTMB(string[] indexValues, bool isNPS, bool isReverse, double[] missingValue) { double[][] scaleGroup = new double[4][]; // top mid bottom missing double[] doubleValues = Common.ConvertDoubleArray(indexValues); Array.Sort(doubleValues); //intValues에서 missing 값들은 아예 제거 doubleValues = doubleValues.Except(missingValue).ToArray(); if (isReverse == false) { //척도 옵션 reverse일때는 Array.Reverse(intValues); 하면 안됨 Array.Reverse(doubleValues); } //TOP, MIDDEL, BOTTOM int[] tmb = new int[] { 0, 0, 0 }; if (isNPS) { tmb[0] = 2; tmb[1] = 2; tmb[2] = 7; } else { switch (doubleValues.Length) { case 4: tmb[0] = 2; tmb[2] = 2; break; case 5: tmb[0] = 2; tmb[1] = 1; tmb[2] = 2; break; case 7: tmb[0] = 3; tmb[1] = 1; tmb[2] = 3; break; case 10: tmb[0] = 3; tmb[1] = 4; tmb[2] = 3; break; case 11: tmb[0] = 4; tmb[1] = 3; tmb[2] = 4; break; } } int scaleLoop = 0; for (int i = 0; i < tmb.Length; i++) { double[] tmbValues = new double[tmb[i]]; for (int j = 0; j < tmb[i]; j++) { tmbValues[j] = doubleValues[scaleLoop]; scaleLoop++; } scaleGroup[i] = tmbValues; } if(missingValue.Length > 0) { scaleGroup[3] = missingValue; } else { scaleGroup[3] = new double[0]; } return scaleGroup; } /// /// Numeric변수에서 사용하는 통계값들 반환 /// /// 값 List /// Key /// 통계값 public string getNumericValue(List doubleList, string numericLabel) { string returnValue = "-"; try { switch (numericLabel) { case "Median": returnValue = Statistics.getMedian(doubleList).ToString(); break; case "SD": returnValue = Statistics.getStandardDeviation(doubleList).ToString(); break; case "Max": returnValue = Statistics.getMax(doubleList).ToString(); break; case "Min": returnValue = Statistics.getMin(doubleList).ToString(); break; case "Mode": returnValue = Statistics.getMode(doubleList).ToString(); break; case "Sum": returnValue = Statistics.getSum(doubleList).ToString(); break; } } catch (Exception ex) { logger.Fatal(this.GetType().Name + " > " + System.Reflection.MethodBase.GetCurrentMethod().Name + " : " + ex.Message); } return returnValue; } /// /// 쿼터 합계값 반환 /// /// 쿼터 값 리스트 /// 쿼터 합계값 public int getQuotaSum(List quotaList) { int sumValue = 0; foreach (var e in quotaList) { if (Common.IsNumeric(e["ori_cnt"].ToString())) { sumValue += Convert.ToInt32(e["ori_cnt"]); } } return sumValue; } } }