Ejemplo n.º 1
0
        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);
                 * }
                 */
            }
        }
Ejemplo n.º 2
0
 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;
     }
 }
Ejemplo n.º 3
0
        private List <PXSqlValue> GetValuesForParsingWhenSelection()
        {
            PXSqlVariable     var            = variable;
            List <PXSqlValue> tempValuesList = new List <PXSqlValue>();

            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());

            //     if ( this.meta.MetaQuery.metaVersionLE("2.0")) {
            if (this.meta.MetaQuery.metaVersionLE("2.1"))
            {
                tempValuesList.Sort(PXSqlValue.SortByValue());
            }

            return(tempValuesList);
        }
Ejemplo n.º 4
0
        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;
        }
Ejemplo n.º 5
0
        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()
Ejemplo n.º 6
0
        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.");
        }