private void CreateSqlString() { int TempNumber = 25; string sqlJoinString = ""; string sqlGroupByString = ""; string tempGroupFactorSQL = ""; foreach (string key in keysInReverseOutputOrder) { PXSqlVariable var = mMeta.Variables[key]; if (!var.IsContentVariable) { #region if (!var.IsContentVariable) var.TempTableNo = TempNumber.ToString(); TempNumber++; if (!var.UsesGrouping) { List <PXSqlValue> valueCodes = var.GetValuesForParsing(); if (valueCodes.Count == 0) { throw new PCAxis.Sql.Exceptions.DbPxsMismatchException(2, var.Name); } if (var.Values.Count != valueCodes.Count) { log.Warn("There seems to be an inconsistency in which values should be included..."); } var.TempTableName = mMeta.MetaQuery.MakeTempTable(var.Name, var.TempTableNo, valueCodes, 500); } else { List <PXSqlGroup> groupCodes = ((PXSqlVariableClassification)var).GetGroupsForParsing(); if (groupCodes.Count == 0) { throw new PCAxis.Sql.Exceptions.DbPxsMismatchException(2, var.Name); } var.TempTableName = mMeta.MetaQuery.MakeTempTable(var.Name, var.TempTableNo, groupCodes, 500, needGroupingFactor && var.UsesGrouping); } #region sql sqlJoinString += " JOIN " + var.TempTableName + " ON dt." + key + " = " + var.TempTableName + ".a" + key + " "; if (useSum) { sqlGroupByString += ", Groupnr" + var.TempTableNo; } if (npm) { //We need the grupp-variables in case of a DataNoteCell if (useSum) { sqlGroupByString += ", Group" + var.TempTableNo; } DataNoteCellId_Columns.Add("Group" + var.TempTableNo); sqlString += "Group" + var.TempTableNo + ", "; } // done:needGroupingFactor bør vel erstattes med needGroupingFactor && <denne variabelen bruker grouping // tempGroupFactorSQL will be used in an UPDATE statement to adjust the GroupFactor in the temptables if (needGroupingFactor && var.UsesGrouping) { if (!String.IsNullOrEmpty(tempGroupFactorSQL)) { tempGroupFactorSQL += " * "; } tempGroupFactorSQL += "MAX(GroupFactor" + var.TempTableNo + ")"; //Max: they should all be equal } #endregion sql #endregion } // (!var.IsContentVariable) else { if (npm) { DataNoteCellId_Columns.Add(""); } log.Debug(" is ContentVariable "); } mValueCount[key] = var.Values.Count; } // foreach (string key in keysInReverseOutputOrder) // INDEXFACTOR string lPrevKey = ""; foreach (string key in keysInReverseOutputOrder) { if (String.IsNullOrEmpty(lPrevKey)) { mIndexFactor.Add(key, 1); } else { mIndexFactor.Add(key, mIndexFactor[lPrevKey] * mValueCount[lPrevKey]); } lPrevKey = key; } // mSize = matrix size, i.e. the number of cells in the resulting matrix mSize = mIndexFactor[lPrevKey] * mValueCount[lPrevKey];// = all ValueCount entries multiplied // CONTENTS foreach (string contCode in contKeys) { sqlString += getContSelectPart(contCode, useSum, npm); } if (useSum) { sqlString += " COUNT(*) AS SqlMarx777777, "; } //else { // sqlString += " 1 SqlMarx777777 , "; //} // MINDEX string mindexSQLString = " ("; foreach (string key in keysInReverseOutputOrder) { if (key.Equals(theKeyOfTheContentsVariableVariable)) { continue; } mindexSQLString += " " + mIndexFactor[key] + " * (Groupnr" + mMeta.Variables[key].TempTableNo + " -1) + "; } mindexSQLString += "0) AS " + MINDEX_COL + " \n"; sqlString += mindexSQLString; if (needGroupingFactor) { sqlString += ", " + tempGroupFactorSQL + " GroupFactor "; } // FROM & JOIN sqlString += getFROMClause() + sqlJoinString; sqlString += "\n WHERE 1=1 "; foreach (string key in keysOfEliminatedByValue) { foreach (PXSqlValue val in mMeta.Variables[key].Values.Values) // there will only be one { sqlString += " AND " + key + " = '" + val.ValueCode + "'"; } } // GROUP BY if (useSum) { sqlString += " GROUP BY " + sqlGroupByString.Substring(1); } //log.Debug("SQL:\n" + sqlString); } // private void CreateSqlString()
public PXSqlData_21(PXSqlMeta_21 mPXSqlMeta) { log.Debug("Start PXSqlData mPXSqlMeta.Name: " + mPXSqlMeta.Name); this.mMeta = mPXSqlMeta; symbols = mMeta.mPxsqlNpm; this.npm = mMeta.SpecCharExists; this.hasGrouping = mMeta.HasGrouping; this.eliminationBySum = mMeta.EliminatedVariablesExist; //System.Console.WriteLine("DEBUG tukler med nmp og summ"); //npm = true; //alwaysUseSum = true; this.useSum = hasGrouping || eliminationBySum; foreach (PXSqlVariable var in mMeta.Stubs) { keysInReverseOutputOrder.Add(var.Name); } foreach (PXSqlVariable var in mMeta.Headings) { keysInReverseOutputOrder.Add(var.Name); } keysInReverseOutputOrder.Reverse(); numberOfOutputVariables = keysInReverseOutputOrder.Count; mValueCount = new Dictionary <string, int>(numberOfOutputVariables); mIndexFactor = new Dictionary <string, int>(numberOfOutputVariables); foreach (PXSqlVariable var in mMeta.Variables.Values) { if (var.IsEliminatedByValue) { log.Debug(var.Name + " Is Eliminated By Value"); keysOfEliminatedByValue.Add(var.Name); } else if (var.IsContentVariable) { log.Debug(var.Name + " Is Content Variable"); theKeyOfTheContentsVariableVariable = var.Name; } else if (!var.isSelected) { log.Debug(var.Name + " Is Eliminated By SUM"); keysOfEliminatedBySum.Add(var.Name); eliminationFactor *= var.TotalNumberOfValuesInDB; } } #region init contKeys if (String.IsNullOrEmpty(theKeyOfTheContentsVariableVariable)) //just one contVar { throw new ApplicationException("Bug"); // the size of ContentsVariableVariable should not influence how it is stored // contKeys.Add( mMeta.FirstContents ); } else { PXSqlVariable theContentsVariable = mMeta.Variables[theKeyOfTheContentsVariableVariable]; foreach (PXSqlValue contCode in theContentsVariable.GetValuesForParsing()) { //contKeys.Add(contCode.ValueCode); contKeys.Add(contCode.ContentsCode); // 2010.05.07 replaces line above because valuecode is now Prescode from contents. New contentscode added to PXSqlValue } foreach (string contCode in theContentsVariable.Values.Keys) { // contKeys.Add(contCode); } } #endregion init contKeys #region init defaults for missing rows //value to be used if row is missing ValueOfCellsInMissingRows = new double[contKeys.Count]; CategoryOfCellsInMissingRows = new string[contKeys.Count]; int contCount2 = 0; anyDefaultMRNotZero = false; // MR = missing record anyDefaultMROfCat3 = false; // MR = missing record foreach (string contCode in contKeys) { PXSqlContent tmpCont = mMeta.Contents[contCode]; log.Debug("PXSqlContents for " + contCode + " PresCellsZero:" + tmpCont.PresCellsZero + " PresMissingLine:" + tmpCont.PresMissingLine); CategoryOfCellsInMissingRows[contCount2] = tmpCont.CategoryOfCellsInMissingRows; ValueOfCellsInMissingRows[contCount2] = tmpCont.ValueOfCellsInMissingRows; if (!CategoryOfCellsInMissingRows[contCount2].Equals("0")) { anyDefaultMRNotZero = true; if (CategoryOfCellsInMissingRows[contCount2].Equals("3")) { anyDefaultMROfCat3 = true; } } contCount2++; } #endregion init defaults for missing rows log.Debug("eliminationFactor: " + eliminationFactor); log.Debug("useSum:" + useSum + " , npm: " + npm); log.Debug("Constructor done."); }