public void sortingXY(ConcurrentDictionary <string, clientMachine.userPreference> userPreference, List <decimal> distinctListChecksum, List <decimal> distinctSet, Dictionary <decimal, decimal> unsorted2SortedCheksum, decimal requestID, string outputFolder, ConcurrentDictionary <string, clientMachine.clientSession> clientSessionVariable, ConcurrentDictionary <decimal, clientMachine.request> requestDict, ConcurrentDictionary <decimal, clientMachine.response> responseDict, Dictionary <int, Dictionary <double, string> > distinctXramKey2Value, Dictionary <int, Dictionary <double, string> > distinctYramKey2Value, Dictionary <int, Dictionary <double, string> > distinctRamKey2Value, Dictionary <int, List <double> > XdistinctList, Dictionary <int, List <double> > YdistinctList, List <int> sortedXdimension, List <int> sortedYdimension, Dictionary <int, Dictionary <double, double> > ramKey2Order, Dictionary <int, Dictionary <double, double> > ramOrder2Key, Dictionary <int, Dictionary <double, double> > distinctXramKey2Order, Dictionary <int, Dictionary <double, double> > distinctXramOrder2Key, Dictionary <int, Dictionary <double, double> > distinctYramKey2Order, Dictionary <int, Dictionary <double, double> > distinctYramOrder2Key, Dictionary <int, List <double> > copyXdistinctList, Dictionary <int, List <double> > copyYdistinctList, List <int> crosstabDimension, List <int> yDimension, Dictionary <int, List <double> > distinctList, List <int> revisedX, List <int> revisedY) { // reorganize master (key to value) for X,Y distinctList by assigned "=" function for (int i = 0; i < crosstabDimension.Count; i++) { distinctXramKey2Order[i] = ramKey2Order[crosstabDimension[i]]; distinctXramOrder2Key[i] = ramOrder2Key[crosstabDimension[i]]; } for (int i = 0; i < yDimension.Count; i++) { // distinctYramKey2Value[i] = ramKey2Valuegz[yDimension[i]]; distinctYramKey2Order[i] = ramKey2Order[yDimension[i]]; distinctYramOrder2Key[i] = ramOrder2Key[yDimension[i]]; } // select X, Y dimension from distinctList to output XdistinctList and YdistinctList Distinct currentdistinct = new Distinct(); copyXdistinctList = currentdistinct.distinctDB(distinctList, distinctRamKey2Value, revisedX); // get distinct distinctList by selected X dimensions copyYdistinctList = currentdistinct.distinctDB(distinctList, distinctRamKey2Value, revisedY); // get distinct distinctList by selected Y dimensions Dictionary <int, List <double> > tempXdistinctList = new Dictionary <int, List <double> >(); Dictionary <int, List <double> > tempYdistinctList = new Dictionary <int, List <double> >(); List <decimal> XdimensionSortingChecksumList = new List <decimal>(); // ChecksumList for Sorting of X dimensions List <decimal> YdimensionSortingChecksumList = new List <decimal>(); // ChecksumList for Sorting Y dimensions List <Sorting> checksum2OrderX = new List <Sorting>(); List <Sorting> checksum2OrderY = new List <Sorting>(); int eachChecksum2OrderRow = 0; StringBuilder csvString = new StringBuilder(); if (requestDict[requestID].sortXdimension == "A" || requestDict[requestID].sortXdimension == "D") // Sort X Dimension //////////////////////////////////////////// { var startSortXTime = DateTime.Now; if (requestDict[requestID].debugOutput == "Y") { if (!Directory.Exists(outputFolder + userPreference["system"].slash + "debug" + userPreference["system"].slash)) { Directory.CreateDirectory(outputFolder + userPreference["system"].slash + "debug" + userPreference["system"].slash); } using (StreamWriter toDisk = new StreamWriter(outputFolder + userPreference["system"].slash + "debug" + userPreference["system"].slash + "XdistinctList.csv")) { csvString.Append("distinctXramKey2Value[0][i]" + "," + "distinctXramKey2Value[i][XdistinctList[i][j]]" + "," + "XdistinctList[i][j]" + Environment.NewLine); for (int i = 0; i < copyXdistinctList.Count; i++) // output XdistinctList { for (int j = 0; j < copyXdistinctList[i].Count; j++) { csvString.Append(distinctXramKey2Value[i][0] + "," + distinctXramKey2Value[i][copyXdistinctList[i][j]] + "," + copyXdistinctList[i][j] + Environment.NewLine); } } toDisk.Write(csvString); toDisk.Close(); csvString.Clear(); } } for (int i = 0; i < copyXdistinctList.Count; i++) // convert key to order and save as tempXdistinctList { tempXdistinctList.Add(i, new List <double>()); for (int j = 0; j < copyXdistinctList[i].Count; j++) { tempXdistinctList[i].Add(distinctXramKey2Order[i][copyXdistinctList[i][j]]); // convert master key to sorting order of the key } } // return Y dimensionSortingChecksumList Distinct getXY = new Distinct(); XdimensionSortingChecksumList = getXY.getXYcheckSumList(tempXdistinctList, distinctXramKey2Value, sortedXdimension); for (int i = 0; i < XdimensionSortingChecksumList.Count; i++) { var checksum2OrderXRow = new Sorting { sortingChecksum = XdimensionSortingChecksumList[i], sortingOrder = i }; checksum2OrderX.Add(checksum2OrderXRow); } if (requestDict[requestID].sortXdimension == "A") // Sort checksum2OrderX by ascending or descending //if (dataSortingOrder[0] == "sortAscending") { var sortChecksum2OrderX = from eachChecksum2OrderX in checksum2OrderX orderby eachChecksum2OrderX.sortingChecksum ascending select eachChecksum2OrderX; outputsortChecksum2OrderX(sortChecksum2OrderX); } if (requestDict[requestID].sortXdimension == "D") { var sortChecksum2OrderX = from eachChecksum2OrderX in checksum2OrderX orderby eachChecksum2OrderX.sortingChecksum descending select eachChecksum2OrderX; outputsortChecksum2OrderX(sortChecksum2OrderX); } } void outputsortChecksum2OrderX(IOrderedEnumerable <Sorting> sortChecksum2OrderX) // output sorting result to csv and to XdistinctList { if (requestDict[requestID].debugOutput == "Y") { if (!Directory.Exists(outputFolder + userPreference["system"].slash + "debug" + userPreference["system"].slash)) { Directory.CreateDirectory(outputFolder + userPreference["system"].slash + "debug" + userPreference["system"].slash); } using (StreamWriter toDisk = new StreamWriter(outputFolder + userPreference["system"].slash + "debug" + userPreference["system"].slash + "sortChecksum2OrderX.csv")) { eachChecksum2OrderRow = 0; csvString.Append("column" + "," + "sortingChecksum" + "," + "sortingOrder" + "," + "XdistinctList[i][eachChecksum2OrderX.sortingOrder]" + "," + "distinctXramKey2Value[i][XdistinctList[i][eachChecksum2OrderX.sortingOrder]]" + Environment.NewLine); for (int i = 0; i < copyXdistinctList.Count; i++) { csvString.Append("0" + "," + "0" + "," + distinctXramKey2Value[i][copyXdistinctList[i][0]] + "," + copyXdistinctList[i][0] + Environment.NewLine); foreach (var eachChecksum2OrderX in sortChecksum2OrderX) { if (eachChecksum2OrderX.sortingOrder != 0) { csvString.Append(distinctXramKey2Value[i][0] + "," + eachChecksum2OrderX.sortingChecksum + "," + eachChecksum2OrderX.sortingOrder + "," + copyXdistinctList[i][eachChecksum2OrderX.sortingOrder] + "," + distinctXramKey2Value[i][copyXdistinctList[i][eachChecksum2OrderX.sortingOrder]] + Environment.NewLine); } } eachChecksum2OrderRow++; } toDisk.Write(csvString); toDisk.Close(); csvString.Clear(); } } eachChecksum2OrderRow = 0; for (int i = 0; i < copyXdistinctList.Count; i++) { XdistinctList.Add(i, new List <double>()); XdistinctList[i].Add(0); foreach (var eachChecksum2OrderX in sortChecksum2OrderX) { if (eachChecksum2OrderX.sortingOrder != 0) { XdistinctList[i].Add(copyXdistinctList[i][eachChecksum2OrderX.sortingOrder]); } eachChecksum2OrderRow++; } } } if (requestDict[requestID].sortYdimension == "A" || requestDict[requestID].sortYdimension == "D") // Sort Y Dimension //////////////////////////////////////////// { var startSortYTime = DateTime.Now; if (requestDict[requestID].debugOutput == "Y") { if (!Directory.Exists(outputFolder + userPreference["system"].slash + "debug" + userPreference["system"].slash)) { Directory.CreateDirectory(outputFolder + userPreference["system"].slash + "debug" + userPreference["system"].slash); } using (StreamWriter toDisk = new StreamWriter(outputFolder + userPreference["system"].slash + "debug" + userPreference["system"].slash + "YdistinctList.csv")) { csvString.Append("distinctYramKey2Value[0][i]" + "," + "distinctYramKey2Value[i][copyYdistinctList[i][j]]" + "," + "YdistinctList[i][j]" + Environment.NewLine); for (int i = 0; i < copyYdistinctList.Count; i++) // output YdistinctList { for (int j = 0; j < copyYdistinctList[i].Count; j++) { csvString.Append(distinctYramKey2Value[i][0] + "," + distinctYramKey2Value[i][copyYdistinctList[i][j]] + "," + copyYdistinctList[i][j] + Environment.NewLine); } } toDisk.Write(csvString); toDisk.Close(); csvString.Clear(); } } for (int i = 0; i < copyYdistinctList.Count; i++) // convert key to order and save as tempYdistinctList { tempYdistinctList.Add(i, new List <double>()); for (int j = 0; j < copyYdistinctList[i].Count; j++) { tempYdistinctList[i].Add(distinctYramKey2Order[i][copyYdistinctList[i][j]]); // convert master key to sorting order of the key } } // return Y dimensionSortingChecksumList Distinct getXY = new Distinct(); YdimensionSortingChecksumList = getXY.getXYcheckSumList(tempYdistinctList, distinctYramKey2Value, sortedYdimension); for (int i = 0; i < YdimensionSortingChecksumList.Count; i++) { var checksum2OrderYRow = new Sorting { sortingChecksum = YdimensionSortingChecksumList[i], sortingOrder = i }; checksum2OrderY.Add(checksum2OrderYRow); } if (requestDict[requestID].sortYdimension == "A") { var sortChecksum2OrderY = from eachChecksum2OrderY in checksum2OrderY orderby eachChecksum2OrderY.sortingChecksum ascending select eachChecksum2OrderY; outputsortChecksum2OrderY(sortChecksum2OrderY); } if (requestDict[requestID].sortYdimension == "D") { var sortChecksum2OrderY = from eachChecksum2OrderY in checksum2OrderY orderby eachChecksum2OrderY.sortingChecksum descending select eachChecksum2OrderY; outputsortChecksum2OrderY(sortChecksum2OrderY); } void outputsortChecksum2OrderY(IOrderedEnumerable <Sorting> sortChecksum2OrderY) // output sorting result to csv and to YdistinctList { if (requestDict[requestID].debugOutput == "Y") { if (!Directory.Exists(outputFolder + userPreference["system"].slash + "debug" + userPreference["system"].slash)) { Directory.CreateDirectory(outputFolder + userPreference["system"].slash + "debug" + userPreference["system"].slash); } using (StreamWriter toDisk = new StreamWriter(outputFolder + userPreference["system"].slash + "debug" + userPreference["system"].slash + "sortChecksum2OrderY.csv")) { eachChecksum2OrderRow = 0; csvString.Append("column" + "," + "sortingChecksum" + "," + "sortingOrder" + "," + "YdistinctList[i][eachChecksum2OrderY.sortingOrder]" + "," + "distinctYramKey2Value[i][YdistinctList[i][eachChecksum2OrderY.sortingOrder]]" + Environment.NewLine); for (int i = 0; i < copyYdistinctList.Count; i++) { csvString.Append("0" + "," + "0" + "," + distinctYramKey2Value[i][copyYdistinctList[i][0]] + "," + copyYdistinctList[i][0] + Environment.NewLine); foreach (var eachChecksum2OrderY in sortChecksum2OrderY) { if (eachChecksum2OrderY.sortingOrder != 0) { csvString.Append(distinctYramKey2Value[i][0] + "," + eachChecksum2OrderY.sortingChecksum + "," + eachChecksum2OrderY.sortingOrder + "," + copyYdistinctList[i][eachChecksum2OrderY.sortingOrder] + "," + distinctYramKey2Value[i][copyYdistinctList[i][eachChecksum2OrderY.sortingOrder]] + Environment.NewLine); } } eachChecksum2OrderRow++; } toDisk.Write(csvString); toDisk.Close(); csvString.Clear(); } } for (int i = 0; i < copyYdistinctList.Count; i++) { foreach (var eachChecksum2OrderY in sortChecksum2OrderY) { var key = distinctListChecksum[eachChecksum2OrderY.sortingOrder]; if (eachChecksum2OrderY.sortingOrder != 0 && !unsorted2SortedCheksum.ContainsKey(key)) { unsorted2SortedCheksum.Add(key, eachChecksum2OrderY.sortingChecksum); distinctSet.Add(key); } } eachChecksum2OrderRow++; } eachChecksum2OrderRow = 0; for (int i = 0; i < copyYdistinctList.Count; i++) { YdistinctList.Add(i, new List <double>()); YdistinctList[i].Add(0); foreach (var eachChecksum2OrderY in sortChecksum2OrderY) { if (eachChecksum2OrderY.sortingOrder != 0) { YdistinctList[i].Add(copyYdistinctList[i][eachChecksum2OrderY.sortingOrder]); } eachChecksum2OrderRow++; } } } } }
public void crosstabByCopyZero(ConcurrentDictionary <string, clientMachine.userPreference> userPreference, Dictionary <int, List <double> > YXMdistinctDrillKey, Dictionary <decimal, int> distinctDimensionChecksumList, Dictionary <double, decimal> crosstabAddress2DrillSetDict, char csvWriteSeparator, string outputFolder, decimal requestID, ConcurrentDictionary <string, clientMachine.clientSession> clientSessionVariable, ConcurrentDictionary <decimal, clientMachine.request> requestDict, List <int> measure, Dictionary <int, List <double> > distinctList, Dictionary <int, List <double> > XdistinctList, Dictionary <int, List <double> > YdistinctList, Dictionary <int, string> sortedRevisedXY, Dictionary <int, Dictionary <double, string> > distinctXramKey2Value, Dictionary <int, Dictionary <double, string> > distinctYramKey2Value, List <int> sortedXdimension, List <int> sortedYdimension, Dictionary <int, List <double> > YXMdistinctList, StringBuilder debug, int xAddressCol, int yAddressCol) { Dictionary <int, List <double> > XofSummarisedList = new Dictionary <int, List <double> >(); // X dimensions of SummarisedFullList Dictionary <int, List <double> > YofSummarisedList = new Dictionary <int, List <double> >(); // Y dimensions of SummarisedFullList List <decimal> XdimensionChecksumList = new List <decimal>(); // ChecksumList for X dimensions List <decimal> YdimensionChecksumList = new List <decimal>(); // ChecksumList for Y dimensions Dictionary <decimal, double> XdimensionChecksum2RecordNo = new Dictionary <decimal, double>(); // ChecksumList for X dimensions --> RecordNo mapping Dictionary <decimal, double> YdimensionChecksum2RecordNo = new Dictionary <decimal, double>(); // ChecksumList for Y dimensions --> RecordNo mapping List <decimal> XofSummarisedListChecksumList = new List <decimal>(); // ChecksumList for X dimensions of SummarisedFullList List <decimal> YofSummarisedListChecksumList = new List <decimal>(); // ChecksumList for Y dimensions of SummarisedFullList int x = 0; int y = 0; for (int i = 0; i < sortedRevisedXY.Count; i++) { if (sortedRevisedXY[i] == "X") { XofSummarisedList.Add(x, new List <double>()); if (requestDict[requestID].debugOutput == "Y") { debug.Append("x " + x + " " + i); debug.Append(Environment.NewLine); } for (int j = 0; j < distinctList[0].Count; j++) { XofSummarisedList[x].Add(distinctList[i][j]); } x++; } if (sortedRevisedXY[i] == "Y") { YofSummarisedList.Add(y, new List <double>()); if (requestDict[requestID].debugOutput == "Y") { debug.Append("y " + y + " " + i); debug.Append(Environment.NewLine); } for (int j = 0; j < distinctList[0].Count; j++) { YofSummarisedList[y].Add(distinctList[i][j]); } y++; } } Distinct getXY = new Distinct(); XdimensionChecksumList = getXY.getXYcheckSumList(XdistinctList, distinctXramKey2Value, sortedXdimension); YdimensionChecksumList = getXY.getXYcheckSumList(YdistinctList, distinctYramKey2Value, sortedYdimension); XofSummarisedListChecksumList = getXY.getXYcheckSumList(XofSummarisedList, distinctXramKey2Value, sortedXdimension); YofSummarisedListChecksumList = getXY.getXYcheckSumList(YofSummarisedList, distinctYramKey2Value, sortedYdimension); ExportCSV currentExport = new ExportCSV(); if (requestDict[requestID].debugOutput == "Y") { currentExport.ramTable2CSV(userPreference, XdistinctList, csvWriteSeparator, outputFolder, userPreference["system"].slash + "Debug" + userPreference["system"].slash + "XdistinctList" + ".csv"); currentExport.ramTable2CSV(userPreference, YdistinctList, csvWriteSeparator, outputFolder, userPreference["system"].slash + "Debug" + userPreference["system"].slash + "YdistinctList" + ".csv"); currentExport.ramTable2CSV(userPreference, XofSummarisedList, csvWriteSeparator, outputFolder, userPreference["system"].slash + "Debug" + userPreference["system"].slash + "XofSummarisedList" + ".csv"); currentExport.ramTable2CSV(userPreference, YofSummarisedList, csvWriteSeparator, outputFolder, userPreference["system"].slash + "Debug" + userPreference["system"].slash + "YofSummarisedList" + ".csv"); } for (int i = 0; i < XdimensionChecksumList.Count; i++) { XdimensionChecksum2RecordNo.Add(XdimensionChecksumList[i], i); } for (int i = 0; i < YdimensionChecksumList.Count; i++) { YdimensionChecksum2RecordNo.Add(YdimensionChecksumList[i], i); } if (requestDict[requestID].debugOutput == "Y") { debug.Append("distinctList: " + distinctList.Count + " distinctList[0]: " + distinctList[0].Count); debug.Append(Environment.NewLine); } if (distinctList.ContainsKey(xAddressCol)) { distinctList.Remove(xAddressCol); distinctList.Add(xAddressCol, new List <double>()); distinctList[xAddressCol].Add(0); } if (!distinctList.ContainsKey(xAddressCol)) { distinctList.Add(xAddressCol, new List <double>()); distinctList[xAddressCol].Add(0); } if (distinctList.ContainsKey(yAddressCol)) { distinctList.Remove(yAddressCol); distinctList.Add(yAddressCol, new List <double>()); distinctList[yAddressCol].Add(0); } if (!distinctList.ContainsKey(yAddressCol)) { distinctList.Add(yAddressCol, new List <double>()); distinctList[yAddressCol].Add(0); } for (int i = 1; i < distinctList[0].Count; i++) { distinctList[xAddressCol].Add(XdimensionChecksum2RecordNo[XofSummarisedListChecksumList[i]]); distinctList[yAddressCol].Add(YdimensionChecksum2RecordNo[YofSummarisedListChecksumList[i]]); } List <decimal> _distinctDimensionChecksumList = new List <decimal>(); foreach (var pair in distinctDimensionChecksumList) { _distinctDimensionChecksumList.Add(pair.Key); } for (int i = 1; i < distinctList[0].Count; i++) { var key = distinctList[xAddressCol][i] * 100000 + distinctList[yAddressCol][i]; crosstabAddress2DrillSetDict.Add(key, _distinctDimensionChecksumList[i - 1]); } if (requestDict[requestID].debugOutput == "Y") { currentExport.ramTable2CSV(userPreference, distinctList, csvWriteSeparator, outputFolder, userPreference["system"].slash + "Debug" + userPreference["system"].slash + "SummarisedListwithX&Yaddress" + ".csv"); } // fill all cell of the final output by zero int start0Col = YdistinctList.Count; int end0Col = YdistinctList.Count + ((XdistinctList[0].Count - 1) * measure.Count); for (int i = 0; i < YdistinctList.Count; i++) { YXMdistinctList[i] = YdistinctList[i]; YXMdistinctDrillKey[i] = YdistinctList[i]; } List <double> source = new List <double>(); for (int j = 0; j < YdistinctList[0].Count; j++) { source.Add(0); } for (int i = start0Col; i < end0Col; i++) // add zero for X and M column { YXMdistinctList.Add(i, new List <double>()); YXMdistinctList[i].AddRange(source); YXMdistinctDrillKey.Add(i, new List <double>()); YXMdistinctDrillKey[i].AddRange(source); } if (requestDict[requestID].debugOutput == "Y") { currentExport.ramTable2CSV(userPreference, YXMdistinctList, csvWriteSeparator, outputFolder, userPreference["system"].slash + "Debug" + userPreference["system"].slash + "YXMdistinctListwithZeroValue" + ".csv"); currentExport.ramTable2CSV(userPreference, YXMdistinctList, csvWriteSeparator, outputFolder, userPreference["system"].slash + "Debug" + userPreference["system"].slash + "YXMdistinctDrillKeywithZeroValue" + ".csv"); } int targetCol; int targetRow; for (int xValue = 1; xValue < XdistinctList[0].Count; xValue++) // fill number if target cell is not zero { for (int m = 0; m < measure.Count; m++) // read distinctList number and write to YXMdistinctList Table Header { targetCol = start0Col + ((xValue - 1) * measure.Count) + m; YXMdistinctList[targetCol][0] = xValue; YXMdistinctDrillKey[targetCol][0] = xValue; } } for (int m = measure.Count; m >= 1; m--) // read distinctList number and write to YXMdistinctList Table Body { for (int YXMrow = 1; YXMrow < distinctList[0].Count; YXMrow++) { targetCol = start0Col + (Convert.ToInt32(distinctList[xAddressCol][YXMrow]) * measure.Count) - m; targetRow = Convert.ToInt32(distinctList[yAddressCol][YXMrow]); YXMdistinctList[targetCol][targetRow] = distinctList[xAddressCol - m][YXMrow]; YXMdistinctDrillKey[targetCol][targetRow] = Convert.ToDouble(crosstabAddress2DrillSetDict[distinctList[xAddressCol][YXMrow] * 100000 + distinctList[yAddressCol][YXMrow]]); } } if (requestDict[requestID].debugOutput == "Y") { currentExport.ramTable2CSV(userPreference, YXMdistinctList, csvWriteSeparator, outputFolder, userPreference["system"].slash + "Debug" + userPreference["system"].slash + "fastYXMdistinctList" + ".csv"); currentExport.ramTable2CSV(userPreference, YXMdistinctDrillKey, csvWriteSeparator, outputFolder, userPreference["system"].slash + "Debug" + userPreference["system"].slash + "fastYXMdistinctDrillKey" + ".csv"); debug.Append("XdimensionChecksumList.Count " + XdimensionChecksumList.Count); debug.Append(Environment.NewLine); debug.Append("YdimensionChecksumList.Count " + YdimensionChecksumList.Count); debug.Append(Environment.NewLine); debug.Append(""); } }