private void SetVariables()
{
//mVariables = new Dictionary<string, PXSqlVariable>();
mVariables = new PXSqlVariables(this);
mVariablesClassification = new PXSqlVariablesClassification();
// Get all the variables connected to the selected maintable
foreach (MainTableVariableRow aTVRow in mMetaQuery.GetMainTableVariableRows(mMainTableId))
{
if (aTVRow.VariableType == mConfig.Codes.VariableTypeT)
{
mTimeVariable = new PXSqlVariableTime(aTVRow, this);
mSqlVariable = mTimeVariable;
mVariables.Add(mSqlVariable.Name, mSqlVariable);
}
else
{ // Should be changed so that variabletype is set separate.
if (!mVariables.ContainsKey(aTVRow.Variable))
{
mSqlVariable = new PXSqlVariableClassification(aTVRow, this);
mVariablesClassification.Add(mSqlVariable.Name, (PXSqlVariableClassification)mSqlVariable);
mVariables.Add(mSqlVariable.Name, mSqlVariable);
}
}
mSqlVariable.VariableType = aTVRow.VariableType;
}
}
public int CompareTo(object obj)
{
//if (this.GetType() != obj.GetType())
//{
// throw new PCAxis.Sql.Exceptions.BugException(10000);
//}
//else
{
PXSqlVariable SqlVariableCompare = (PXSqlVariable)obj;
return(this.Index.CompareTo(SqlVariableCompare.Index)); //will not compare time-,classification and contens
// return ((PXSqlVariable)this).Index.CompareTo(SqlVariableCompare.Index);
/*if (this.Index == sqlVariableCompare.Index)
* {
* return(0);
* }
* else if(this.Index > sqlVariableCompare.Index)
* {
* return(1);
* }
* else if (this.Index < sqlVariableCompare.Index)
* {
* return(-1);
* }
* else
* {
* return(-1);
* }
*/
}
}
internal PXSqlNotes(PXSqlMeta_22 mMeta, string mMainTableId, bool inPresentationModus)
{
this.mMeta = mMeta;
this.mVariables = mMeta.Variables;
this.mSubTables = mMeta.SubTables;
this.mContents = mMeta.Contents;
this.mTimeVariable = mMeta.TimeVariable;
this.mMainTableId = mMainTableId;
this.showFootnotesCodes = new StringCollection();
if (inPresentationModus)
{
showFootnotesCodes.Add(mMeta.Config.Codes.FootnoteShowP);
}
else
{
showFootnotesCodes.Add(mMeta.Config.Codes.FootnoteShowS);
}
showFootnotesCodes.Add(mMeta.Config.Codes.FootnoteShowB);
allRelevantFoonotes = mMeta.MetaQuery.GetRelevantFoonotes(this.mMainTableId);
//Adding footnotes for grouping
allRelevantFoonotes.AddRange(this.getRelevantGroupingNotes(inPresentationModus));
this.SetFootNotes();
}
private List <PXSqlValue> GetValuesForParsingWhenSelection()
{
PXSqlVariable var = variable;
List <PXSqlValue> tempValuesList = new List <PXSqlValue>();
if (this.isHierarchy)
{
tempValuesList = GetHierarchicalValues(rootGroup, tempValuesList);
}
else
{
if (this.mIncludeType == GroupingIncludesType.AggregatedValues)
{
foreach (PXSqlGroup group in this.mGroups)
{
tempValuesList.Add(var.Values[group.ParentCode]); //todo; sortert etter gruppe sorteringskode
}
}
else if (this.mIncludeType == GroupingIncludesType.SingleValues)
{
foreach (PXSqlGroup group in this.mGroups)
{
foreach (string childCode in group.ChildCodes)
{
tempValuesList.Add(var.Values[childCode]); //todo; sortert etter gruppe sorteringskode
}
}
}
else if (this.IncludeType == GroupingIncludesType.All)
{
foreach (PXSqlGroup group in this.mGroups)
{
tempValuesList.Add(var.Values[group.ParentCode]); //todo; sortert etter gruppe sorteringskode
foreach (string childCode in group.ChildCodes)
{
tempValuesList.Add(var.Values[childCode]); //todo; sortert etter gruppe sorteringskode
}
}
}
//tempValuesList.Sort(PXSqlValue.SortByVsValue());
//TODO; Hvorfor hadde Linja under en if ( this.meta.MetaQuery.metaVersionLE("2.1")) { rundt seg
tempValuesList.Sort(PXSqlValue.SortByValue());
}
return(tempValuesList);
}
private void SetContents()
{
BasicValueType[] contentsInPxs = new BasicValueType[0];
if (this.ConstructedFromPxs)
{
contentsInPxs = mPxsFile.Query.Contents.Content;
}
List <PXSqlContent> mTempContentsList = GetSortedContentsList(mMainTableId, this.ConstructedFromPxs, contentsInPxs);
mFirstContents = mTempContentsList[0].Contents;
this.mContents = new Dictionary <string, PXSqlContent>();
foreach (PXSqlContent sortedCont in mTempContentsList)
{
sortedCont.AdjustPresDecimalsToCommonDecimals(this.mDecimalHandler.ShowDecimals);
mContents.Add(sortedCont.Contents, sortedCont);
}
PxSqlValues mValues = new PxSqlValues();
int counter = 0;
foreach (PXSqlContent content in mContents.Values)
{
mValue = new PXSqlValue(content, counter);
mValues.Add(mValue.ValueCode, mValue);
counter++;
}
mSqlVariable = new PXSqlVariableContents(mContVariableCode, this);
mSqlVariable.Values = mValues;
mVariables.Add(mSqlVariable.Name, mSqlVariable);
mContentsVariable = mSqlVariable;
}
private void CreateSqlString()
{
int TempNumber = 25;
string sqlJoinString = "";
string sqlGroupByString = "";
string tempGroupFactorSQL = "";
foreach (string key in variableIDsInReverseOutputOrder)
{
PXSqlVariable var = mMeta.Variables[key];
if (!var.IsContentVariable)
{
#region if (!var.IsContentVariable)
var.TempTableNo = TempNumber.ToString();
TempNumber++;
if (var.UsesGroupingOnNonstoredData())
{
List <PXSqlGroup> groupCodes = ((PXSqlVariableClassification)var).GetGroupsForDataParsing();
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.UsesGroupingOnNonstoredData());
}
else
{
List <PXSqlValue> valueCodes = var.GetValuesForParsing();
if (valueCodes.Count == 0)
{
throw new PCAxis.Sql.Exceptions.DbPxsMismatchException(2, var.Name);
}
var.TempTableName = mMeta.MetaQuery.MakeTempTable(var.Name, var.TempTableNo, valueCodes, 500);
}
#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 + ", ";
}
// tempGroupFactorSQL will be used in an UPDATE statement to adjust the GroupFactor in the temptables
if (needGroupingFactor && var.UsesGroupingOnNonstoredData())
{
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 variableIDsInReverseOutputOrder)
{
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 variableIDsInReverseOutputOrder)
{
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_22(PXSqlMeta_22 mPXSqlMeta)
{
log.Debug("Start PXSqlData mPXSqlMeta.Name: " + mPXSqlMeta.Name);
this.mMeta = mPXSqlMeta;
symbols = mMeta.mPxsqlNpm;
this.npm = mMeta.SpecCharExists;
this.hasGrouping = mMeta.Variables.HasAnyoneGroupingOnNonstoredData();
// .HasAnyoneGrouping();
this.eliminationBySum = mMeta.EliminatedVariablesExist;
//System.Console.WriteLine("DEBUG tukler med nmp og summ");
//npm = true;
//alwaysUseSum = true;
this.useSum = hasGrouping || eliminationBySum;
variableIDsInReverseOutputOrder = mMeta.GetVariableIDsInReverseOutputOrder();
numberOfOutputVariables = variableIDsInReverseOutputOrder.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.");
}