Exemple #1
0
        /// <summary>
        /// Insert a row into the target-assay association table
        /// </summary>
        /// <param name="toks"></param>
        /// <param name="dao"></param>

        public static void InsertCommonAssayAttributes(
            AssayAttributes taa,
            DbCommandMx dao)
        {
            string names  = "";
            string values = "";

            AddInsertColumn("id", ref names, ref values, taa.Id);
            AddInsertColumn("assy_db", ref names, ref values, taa.AssayDatabase);
            AddInsertColumn("assy_id2", ref names, ref values, taa.AssayId2);
            AddInsertColumn("assy_id_nbr", ref names, ref values, taa.AssayIdNbr);
            AddInsertColumn("assy_id_txt", ref names, ref values, taa.AssayIdTxt);
            AddInsertColumn("assy_nm", ref names, ref values, taa.AssayName);
            AddInsertColumn("assy_desc", ref names, ref values, taa.AssayDesc);
            AddInsertColumn("assy_src", ref names, ref values, taa.AssaySource);
            AddInsertColumn("assy_typ", ref names, ref values, taa.AssayType);
            AddInsertColumn("assy_mode", ref names, ref values, taa.AssayMode);
            AddInsertColumn("assy_sts", ref names, ref values, taa.AssayStatus);
            AddInsertColumn("assy_sum_lvl", ref names, ref values, taa.SummarizedAvailable ? 1 : 0);
            AddInsertColumn("assy_gene_cnt", ref names, ref values, taa.GeneCount);
            AddInsertColumn("rslt_typ", ref names, ref values, taa.ResultTypeConcType);
            AddInsertColumn("rslt_typ_nm", ref names, ref values, taa.ResultName);
            AddInsertColumn("rslt_typ_id2", ref names, ref values, taa.ResultTypeId2);
            AddInsertColumn("rslt_typ_id_nbr", ref names, ref values, taa.ResultTypeIdNbr);
            AddInsertColumn("rslt_typ_id_txt", ref names, ref values, taa.ResultTypeIdTxt);
            AddInsertColumn("rslt_uom", ref names, ref values, taa.ResultTypeUnits);
            AddInsertColumn("conc_uom", ref names, ref values, taa.ResultTypeConcUnits);

            AddInsertColumn("top_lvl_rslt", ref names, ref values, taa.TopLevelResult);
            AddInsertColumn("remppd", ref names, ref values, taa.Remapped);
            AddInsertColumn("mltplxd", ref names, ref values, taa.Multiplexed);
            AddInsertColumn("rvwd", ref names, ref values, taa.Reviewed);
            AddInsertColumn("cmpds_assyd", ref names, ref values, taa.CompoundsAssayed);
            AddInsertColumn("prflng_assy", ref names, ref values, taa.ProfilingAssay);
            AddInsertColumn("gene_id", ref names, ref values, taa.GeneId);
            AddInsertColumn("gene_symbl", ref names, ref values, taa.GeneSymbol);
            AddInsertColumn("gene_desc", ref names, ref values, taa.GeneDescription);
            AddInsertColumn("gene_fmly", ref names, ref values, taa.GeneFamily);

            AddInsertColumn("rslt_cnt", ref names, ref values, taa.ResultCount);
            AddInsertColumn("assy_updt_dt", ref names, ref values, taa.AssayUpdateDate);
            AddInsertColumn("assn_src", ref names, ref values, taa.AssociationSource);
            AddInsertColumn("assn_cnflct", ref names, ref values, taa.AssociationConflict);

            AddInsertColumn("x", ref names, ref values, taa.TargetMapX);
            AddInsertColumn("y", ref names, ref values, taa.TargetMapY);
            AddInsertColumn("z", ref names, ref values, taa.TargetMapZ);

            string sql = "insert into mbs_owner.cmn_assy_atrbts (" + names + ") " +
                         "values (" + values + ")";

//			sql = AssayAttributesDao.AdjustAssayAttrsTableName(sql);

            dao.Prepare(sql);
            dao.ExecuteNonReader();
            return;
        }
Exemple #2
0
        /// <summary>
        /// Update Common Assay Attributes table (mbs_owner.cmn_assy_atrbts)
        ///
        /// Command: Update AssayAttributesTable
        ///
        /// This command builds an entry (or two) in the cmn_assy_atrbts table for each assay
        /// referenced in the Mobius contents tree that reports an SP or CRC value as determined
        /// by the associated metafactory and available in each metatable.
        /// If the gene target associated with an assay can be identified then information on that
        /// gene is included as well.
        ///
        /// Additional gene information may come from the metadata for a source such as the results
        /// warehouse.
        ///
        /// Note that this function must be run under an account that has access to all restricted data so that it
        /// can properly see what's available and build the table.
        /// </summary>
        /// <param name="lex"></param>
        /// <returns></returns>

        public static string UpdateAssayAttributesTable(string args)
        {
            MetaTable       mt;
            MetaColumn      mc;
            AssayAttributes aa, aa2;

            Dictionary <int, int>    geneIdCounts = new Dictionary <int, int>();        // genes and counts keyed by entrez gene id
            Dictionary <int, double> targetMapXDict;                                    // dendogram X coord keyed by gene id
            Dictionary <int, double> targetMapYDict;                                    // dendogram Y coord keyed by gene id

            Dictionary <string, int> targetTypeCounts = new Dictionary <string, int>(); // target types and counts
            Dictionary <int, string> assayTypeDict;
            Dictionary <int, string> assayModeDict;

            //UnpivotedAssayResult rr, rr2;
            List <string> toks;
            string        mtName, tableNamePrefix, key, fileName, msg;
            bool          crcExists, spExists, isSummary;
            int           tableId, step, ri, resultTypeId, assayRowCount;

            Log = new LogFile(ServicesDirs.LogDir + @"\UpdateCommonAssayAttributes.log");
            Log.ResetFile();

// Get list of all of the assays in the tree

            LogMessage("Accumulating assays...");

            HashSet <string> mtNameHash = new HashSet <string>();

            foreach (MetaTreeNode mtn0 in MetaTree.Nodes.Values)
            {
                if (mtn0.Type != MetaTreeNodeType.MetaTable)
                {
                    continue;
                }

                mtName = mtn0.Target;

                if (AssayMetaData.IsAssayMetaTableName(mtName))
                {
                    mtNameHash.Add(mtName);
                }
            }

            bool debug = false;             // set to true to debug with limited list of assays from below

            if (debug)
            {
                mtNameHash.Clear();

                //assayHash.Add("ASSAY_1");
                //assayHash.Add("ASSAY_2");
                //assayHash.Add("ASSAY_3");
            }

            // Get other informatin needed from AssayMetadata

            LogMessage("Reading AssayMetadata ASSAY metadata...");
            Dictionary <int, AssayDbMetadata> assayMetadataAssayDict =            // get assays and associated target/gene information
                                                                       AssayMetadataDao.GetAssayTargetGeneData();

            LogMessage("Getting AssayMetadata result types...");
            Dictionary <int, AssayDbResultType> resultTypeDict = AssayMetadataDao.GetResultTypeDict();

            LogMessage("Getting assay types and modes...");
            AssayMetadataDao.GetAssayTypesAndModes(out assayTypeDict, out assayModeDict);

            LogMessage("Getting gene dendogram coordinates...");
            try
            {
                AssayMetadataDao.GetImageCoords(out targetMapXDict, out targetMapYDict);
            }
            catch (Exception ex)             // may fail if problem with data source
            {
                LogMessage(DebugLog.FormatExceptionMessage(ex, true));
                targetMapXDict = new Dictionary <int, double>();
                targetMapYDict = new Dictionary <int, double>();
            }


            // Process each assay

            int metatablesFound = 0, metatablesNotFound = 0;

            int assaysProcessed            = 0;
            int assaysWithGenes            = 0;
            int assaysWithGeneCoords       = 0;
            int assaysWithTargets          = 0;
            int assaysWithSpOnly           = 0;
            int assaysWithCrcOnly          = 0;
            int assaysWithNoCrcSP          = 0;
            int assaysWithOtherTypes       = 0;
            int assaysWithCrcAndSp         = 0;
            int assaysWithNoKeyTypes       = 0;
            int assaysWithProcessingErrors = 0;

            Dictionary <string, int> CrcAssayCnt = new Dictionary <string, int>()
            {
                { "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
            };
            Dictionary <string, int> SpAssayCnt = new Dictionary <string, int>()
            {
                { "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
            };
            Dictionary <string, int> OtherAssayCnt = new Dictionary <string, int>()
            {
                { "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
            };

            List <AssayAttributes> resultTypeRows = new List <AssayAttributes>();
            List <AssayAttributes> geneRows       = new List <AssayAttributes>();
            List <AssayAttributes> dbRows         = new List <AssayAttributes>();

            string copyUpdateMsg = "";

            foreach (string mtName0 in mtNameHash)
            {
                AssayMetaData assayMetaData = null;                 // metadata for assay
                bool          isAssay       = false;
                int           assayIdNbr    = NullValue.NullNumber;
                int           assayId       = NullValue.NullNumber;

                //if (assaysProcessed >= 100) break; // debug

                mtName = mtName0;
                MetaTable.ParseMetaTableName(mtName, out tableNamePrefix, out tableId, out isSummary);

                string resultType = "";
                string rtId = "";
                string assayName, assayDb;

                mt = MetaTableCollection.Get(mtName);                 // get metatable
                if (mt == null)
                {
                    metatablesNotFound++;
                    LogMessage("MetaTable not found: " + mtName);
                    continue;
                }

                metatablesFound++;

                if (mt.Code == "")
                {
                    continue;                         // must be single pivoted assay
                }
                assayDb    = "ASSAY_DB";              // customize
                assayIdNbr = -1;

                if (UalUtil.IClient != null && UalUtil.IClient.Attended)
                {
                    UAL.Progress.Show((assaysProcessed + 1).ToString() + " / " + mtNameHash.Count + " - " + mt.Name + "\r\n" + mt.Label);
                }

                aa = new AssayAttributes();
                aa.AssayDatabase = assayDb;
                aa.AssayIdNbr    = assayIdNbr;         // data-source-specific assay Id
                aa.AssayIdTxt    = mt.Name;            // store ASSAY_1234  type table name
                aa.AssayId2      = assayId;            // any associated assay id

                if (isAssay)
                {
                    aa.AssayName = assayMetaData.Name;                     // name from AssayMetadata
                }
                else
                {
                    aa.AssayName = MetaTable.RemoveSuffixesFromName(mt.Label);                  // name from metatable
                }
                if (mt.SummarizedExists)
                {
                    aa.SummarizedAvailable = true;
                }
                else
                {
                    aa.SummarizedAvailable = false;
                }

                if (isAssay)
                {
                    if (assayTypeDict.ContainsKey(tableId))
                    {
                        aa.AssayType = assayTypeDict[tableId];
                    }

                    if (assayModeDict.ContainsKey(tableId))
                    {
                        aa.AssayMode = assayModeDict[tableId];
                    }

                    aa.AssaySource       = AssayMetaData.GetAssaySource(tableId);
                    aa.AssociationSource = "TODO";                     // customize
                }

                aa.AssayStatus = "Active";                 // say all active for now

                MetaTableStats mts = MetaTableFactory.GetStats(mtName);
                if (mts != null)
                {
                    aa.ResultCount     = (int)mts.RowCount;
                    aa.AssayUpdateDate = mts.UpdateDateTime;
                }
                else
                {
                    aa.ResultCount = 0;                  // assume no results if no stats
                }
                if (mt.DescriptionIsAvailable())         // use description from Mobius
                {
                    aa.AssayDesc = "Y";
                }

                if (String.IsNullOrEmpty(aa.GeneFamily))
                {
                    aa.GeneFamily = "Unknown";                              // set these to "Unknown" rather than null
                }
                if (String.IsNullOrEmpty(aa.AssayType))
                {
                    aa.AssayType = "UNKNOWN";                             // upper case UNKNOWN
                }
                if (String.IsNullOrEmpty(aa.AssayMode))
                {
                    aa.AssayMode = "UNKNOWN";                             // upper case UNKNOWN
                }
// Step1: Add a row for primary & any secondary results

                resultTypeRows.Clear();
                MetaColumn       firstResultCol = null, firstKeyResultCol = null, firstOtherKeyResultCol = null;
                string           resultTypeConcType;
                HashSet <string> keyResultTypeCodes = new HashSet <string>();
                int spCnt = 0, crcCnt = 0, otherCnt = 0;

                for (int mci = 0; mci < mt.MetaColumns.Count; mci++)                 // pick first col with result code (could also check summarization method)
                {
                    mc = mt.MetaColumns[mci];

                    if (Lex.IsUndefined(mc.ResultCode))
                    {
                        continue;                                                     // must have code defined
                    }
                    if (keyResultTypeCodes.Contains(mc.ResultCode))
                    {
                        continue;                                                                 // and not included so far
                    }
                    if (mc.InitialSelection != ColumnSelectionEnum.Selected)
                    {
                        continue;                                                                          // selected only
                    }
                    if (firstResultCol == null)
                    {
                        firstResultCol = mc;
                    }

                    if (!IsKeyResultType(mc, out resultTypeConcType))
                    {
                        continue;
                    }

                    if (firstKeyResultCol == null)
                    {
                        firstKeyResultCol = mc;
                    }

                    keyResultTypeCodes.Add(mc.ResultCode);

                    aa2 = aa.Clone();

                    if (resultTypeRows.Count == 0)
                    {
                        aa2.TopLevelResult = "Y";
                    }
                    else
                    {
                        aa2.TopLevelResult = "N";
                    }

                    aa2.ResultTypeId2   = GetAssayResultTypeId(mc);    // AssayMetadata result type id
                    aa2.ResultTypeIdNbr = GetInternalResultTypeId(mc); // Internal database result type id
                    aa2.ResultTypeIdTxt = mc.Name;                     // Mobius column name

                    if (isAssay && resultTypeDict.ContainsKey(aa2.ResultTypeId2))
                    {
                        aa2.ResultName = resultTypeDict[aa2.ResultTypeId2].Name;                         // use name from AssayMetadata result type dict
                    }
                    else
                    {
                        aa2.ResultName = mc.Label;                      // use label from Mobius
                    }
                    aa2.ResultTypeUnits = mc.Units;                     // result units

                    if (Lex.Eq(resultTypeConcType, "SP"))
                    {
                        aa2.ResultTypeConcType = "SP";
                        spCnt++;
                    }

                    else if (Lex.Eq(resultTypeConcType, "CRC"))
                    {
                        aa2.ResultTypeConcType = "CRC";
                        crcCnt++;
                    }

                    else
                    {
                        aa2.ResultTypeConcType = "";
                        otherCnt++;
                        if (firstOtherKeyResultCol == null)
                        {
                            firstOtherKeyResultCol = mc;
                        }
                    }

                    aa2.ResultTypeConcUnits = "";                     // todo

                    resultTypeRows.Add(aa2);
                }

                if (resultTypeRows.Count >= 1)
                {
                    if (crcCnt > 0)
                    {
                        CrcAssayCnt[assayDb]++;                                 // count primary type by db
                    }
                    else if (spCnt > 0)
                    {
                        SpAssayCnt[assayDb]++;
                    }
                    else
                    {
                        OtherAssayCnt[assayDb]++;
                    }

                    if (crcCnt > 0 && spCnt == 0)
                    {
                        assaysWithCrcOnly++;                                               // count overall primary/secondary types
                    }
                    else if (crcCnt == 0 && spCnt > 0)
                    {
                        assaysWithSpOnly++;
                    }
                    else if (crcCnt > 0 && spCnt > 0)
                    {
                        assaysWithCrcAndSp++;
                    }

                    if (crcCnt == 0 && spCnt == 0)                     // no SP or CRC result types
                    {
                        assaysWithNoCrcSP++;
                        mc = firstKeyResultCol;
                        LogMessage("Assay with No SP/CRC key results: " + mt.Name + "." + mc.Name + " (" + mc.ResultCode + "), " + mt.Label + "." + mc.Label);
                    }

                    else if (otherCnt > 0)                     // no SP or CRC result types
                    {
                        assaysWithOtherTypes++;
                        mc = firstOtherKeyResultCol;
                        LogMessage("Non SP/CRC key result: " + mt.Name + "." + mc.Name + " (" + mc.ResultCode + "), " + mt.Label + "." + mc.Label);
                    }
                }

                else                 // no key result type
                {
                    aa2 = aa.Clone();
                    resultTypeRows.Add(aa2);                     // include row for step1

                    OtherAssayCnt[assayDb]++;
                    assaysWithNoKeyTypes++;
                    LogMessage("No key result type for metatable: " + mt.Name + ", " + mt.Label);
                }

// Build a step2 row for each target/gene

                geneRows.Clear();
                List <AssayTarget> targets = new List <AssayTarget>();
                int geneCount = 0;

                if (isAssay)
                {
                    targets = assayMetaData.Targets;
                }
                if (targets.Count > 0)
                {
                    assaysWithTargets++;
                }

                foreach (AssayTarget target in targets)
                {
                    aa            = new AssayAttributes();
                    aa.GeneFamily = target.TargetTypeShortName;                     // count target type occurance
                    if (Lex.IsUndefined(aa.GeneFamily))
                    {
                        aa.GeneFamily = "Unknown";
                    }
                    if (!targetTypeCounts.ContainsKey(aa.GeneFamily))
                    {
                        targetTypeCounts[aa.GeneFamily] = 0;
                    }
                    targetTypeCounts[aa.GeneFamily]++;

                    if (target.Genes == null || target.Genes.Count == 0)                     // if no genes add a single target row
                    {
                        geneRows.Add(aa);
                        continue;
                    }

                    foreach (AssayGene rg in target.Genes)
                    {
                        if (!Lex.IsDefined(rg.GeneSymbol))
                        {
                            continue;
                        }

                        aa2 = aa.Clone();
                        geneRows.Add(aa2);

                        aa2.GeneSymbol = rg.GeneSymbol;
                        int.TryParse(rg.GeneId, out aa2.GeneId);

                        if (aa2.GeneId > 0 && targetMapXDict.ContainsKey(aa2.GeneId))
                        {
                            aa2.TargetMapX = targetMapXDict[aa2.GeneId];
                            aa2.TargetMapY = targetMapYDict[aa2.GeneId];
                            if (geneCount == 0)
                            {
                                assaysWithGeneCoords++;
                            }
                        }

                        if (!geneIdCounts.ContainsKey(aa2.GeneId))                         // count gene occurance
                        {
                            geneIdCounts[aa2.GeneId] = 0;
                        }
                        geneIdCounts[aa2.GeneId]++;

                        if (geneCount == 0)
                        {
                            assaysWithGenes++;
                        }

                        geneCount++;
                    }
                }

                if (geneRows.Count == 0)                 // if no step 2 rows (i.e. no targets), create a single step2 row
                {
                    aa = new AssayAttributes();
                    geneRows.Add(aa);
                }

// Combine key result types with target/genes

                for (int i1 = 0; i1 < resultTypeRows.Count; i1++)
                {
                    AssayAttributes s1aa = resultTypeRows[i1];
                    for (int i2 = 0; i2 < geneRows.Count; i2++)
                    {
                        AssayAttributes s2aa = geneRows[i2];

                        aa            = s1aa.Clone();
                        aa.GeneId     = s2aa.GeneId;
                        aa.GeneSymbol = s2aa.GeneSymbol;
                        aa.GeneFamily = s2aa.GeneFamily;

                        aa.TargetMapX = s2aa.TargetMapX;
                        aa.TargetMapY = s2aa.TargetMapY;

                        aa.GeneCount = geneCount;

                        if (i2 > 0)
                        {
                            aa.GeneCount = -geneCount;                                 // negative for other than 1st gene
                        }
                        dbRows.Add(aa);
                    }
                }

                assaysProcessed++;
            }

            // Update table

            bool updateTable = true;             // set to false for debug

            if (dbRows.Count <= 0)
            {
                LogMessage("No rows in new dataset, table not updated");
            }

            else if (updateTable)
            {
                LogMessage("Deleting existing data...");
                DbCommandMx dao = new DbCommandMx();
                string      sql = "delete from mbs_owner.cmn_assy_atrbts";

                sql = AssayAttributesDao.AdjustAssayAttrsTableName(sql);

                dao.Prepare(sql);
                dao.BeginTransaction();
                int delCnt = dao.ExecuteNonReader();

                LogMessage("Inserting new data...");
                int t0 = TimeOfDay.Milliseconds();
                for (ri = 0; ri < dbRows.Count; ri++)
                {
                    aa    = dbRows[ri];
                    aa.Id = ri + 1;

                    //aa.Id += 10000; // debug

                    if (aa.GeneSymbol != null)
                    {
                        aa.GeneSymbol = aa.GeneSymbol.ToUpper();                                            // be sure key match cols are upper case
                    }
                    if (aa.GeneFamily != null)
                    {
                        aa.GeneFamily = aa.GeneFamily.ToUpper();
                    }
                    if (aa.GeneFamilyTargetSymbol != null)
                    {
                        aa.GeneFamilyTargetSymbol = aa.GeneFamilyTargetSymbol.ToUpper();
                    }
                    if (aa.ResultTypeConcType != null)
                    {
                        aa.ResultTypeConcType = aa.ResultTypeConcType.ToUpper();
                    }
                    if (aa.AssayType != null)
                    {
                        aa.AssayType = aa.AssayType.ToUpper();
                    }
                    if (aa.AssayMode != null)
                    {
                        aa.AssayMode = aa.AssayMode.ToUpper();
                    }

                    AssayAttributesDao.InsertCommonAssayAttributes(aa, dao);
                    if (TimeOfDay.Milliseconds() - t0 > 1000)
                    {
                        //Progress.Show("Inserting new data " + (ri + 1) + "/" + rows.Count + "...");
                        t0 = TimeOfDay.Milliseconds();
                    }
                }

                dao.Commit();
                dao.Dispose();

                copyUpdateMsg = UpdateCmnAssyAtrbtsCopies();
            }

            string response =
                "----------------------------------\r\n" +
                "Assays processed: " + assaysProcessed + "\r\n" +
                "Assays with processing errors: " + assaysWithProcessingErrors + "\r\n" +
                "Rows inserted: " + dbRows.Count + "\r\n" +
                copyUpdateMsg +
                "----------------------------------\r\n" +
                "Assays with CRC only: " + assaysWithCrcOnly + "\r\n" +
                "Assays with SP only: " + assaysWithSpOnly + "\r\n" +
                "Assays with CRC and SP: " + assaysWithCrcAndSp + "\r\n" +
                "Assays with no CRC or SP: " + assaysWithNoCrcSP + "\r\n" +
                "Assays with non CRC/SP key types: " + assaysWithOtherTypes + "\r\n" +
                "Assays with no key types: " + assaysWithNoKeyTypes + "\r\n" +
                "----------------------------------\r\n" +
                "Assays with targets defined: " + assaysWithTargets + "\r\n" +
                "Assays with genes defined: " + assaysWithGenes + "\r\n" +
                "Assays with gene map coordinates: " + assaysWithGeneCoords + "\r\n" +
                "----------------------------------\r\n" +
                //"CRC Assays: " + CrcAssayCnt["ASSAY"] + "\r\n" +
                //"SP  Assays: " + SpAssayCnt["ASSAY"] + "\r\n" +
                //"??? Assays: " + OtherAssayCnt["ASSAY"] + "\r\n" +
                "----------------------------------";

            LogMessage("\r\n" + response);

            UAL.Progress.Hide();
            return(response);
        }
Exemple #3
0
		/// <summary>
		/// Execute query
		/// </summary>
		/// <param name="eqp"></param>

		public override void ExecuteQuery(
				ExecuteQueryParms eqp)
		{
			MetaTable mt;
			MetaColumn mc;
			Query q;
			QueryTable qt;
			QueryColumn qc;
			ResultsPage rp;
			ResultsViewProps view;
			CompoundStructureActivityData cd1, cd2;

			bool smallerIsbetter;
			double r1, r2, r3, r4;
			int di, di2, pdi, pdi2, i3;
			string tok;

			qt = eqp.QueryTable;
			qc = qt.GetQueryColumnByNameWithException(SasMapParms.ParametersMetaColumnName);
			AssertMx.IsDefined(qc.Criteria, qc.Label + " criteria not defined");

			if (Lex.Eq(qc.Criteria, LastCriteriaString)) // if same criteria as last time then use existing data
			{
				VoListPos = -1; // init list position
				return;
			}

			VoList = new List<object[]>();
			VoListPos = -1; // init list position

			LastCriteriaString = qc.Criteria;
			ParsedSingleCriteria psc = ParsedSingleCriteria.Parse(qc);
			SMP = SasMapParms.Deserialize(psc.Value);

			mc = SMP.EndpointMc;
			smallerIsbetter = mc.MultiPoint;

			List<CompoundStructureActivityData> ds1 = ReadData(SMP); // read in the data to analyze
			if (ds1 == null || ds1.Count == 0) return; //  throw new QueryException("No data retrieved");

			List<CompoundStructureActivityData> ds2 = ds1; // just one set for now

			// Calculate difference or ratio coefficents for each pair

			List<PairData> pd = new List<PairData>();
			int minCoef = -1; // index of minimum coefficent selected so far
			double molFactor = AssayAttributes.GetMolarConcFactor(SMP.EndpointMc);

			for (di = 0; di < ds1.Count; di++)
			{ // process all compounds in 1st set
				//		if (ds1[di].Nearest == 0) continue; // any data?
				if (ds2 == ds1) di2 = di + 1; // only do lower rt diagonal if one dataset
				else di2 = 0; // must do all compares, check for dups later

				for ( /* start at di2 */; di2 < ds2.Count; di2++)
				{
					//			if (ds2[di2].Nearest == 0) continue; // any data?
					if (ds1[di].Cid == ds2[di2].Cid) continue; // avoid self compare

					double sim = // similarity
					 CalculateSimilarity(ds1[di], ds2[di2]);
					//if (sim==1.0 && !stereo) // eliminate stereo pairs if requested
					// continue; // a more careful check may be needed

					if (sim < SMP.MinimumSimilarity) continue; // below cutoff value?

					double denom = 1 - sim; // denominator is 1 - sim
					if (denom == 0) denom = .00000000001f; // avoid divide by zero

					double actChange = 0;

					if (smallerIsbetter && ds1[di].Activity < ds2[di2].Activity)
					{
						cd1 = ds1[di];
						cd2 = ds2[di2];
					}

					else
					{
						cd1 = ds2[di2];
						cd2 = ds1[di];
					}

					double a1 = cd1.Activity;
					double a2 = cd2.Activity;

					if (a1 == NullValue.NullNumber || a2 == NullValue.NullNumber)
						actChange = 0;

					else switch (SMP.ActDiffCalcType)
						{
							case ActDiffCalcType.SimpleDifference: // activity difference
								{
									actChange = a1 - a2;
									break;
								}

							case ActDiffCalcType.NegativeLog:
								{
									actChange = -Math.Log10(a1) - -Math.Log10(a2);
									break;
								}

							case ActDiffCalcType.MolarNegativeLog:
								{
									actChange = (-Math.Log10(a1 * molFactor)) - (-Math.Log10(a2 * molFactor));
									break;
								}

							case ActDiffCalcType.Ratio: // activity ratio
								{
									r1 = a1;
									if (r1 == 0) r1 = .00000000001f;
									r2 = a2;
									if (r2 == 0) r2 = .00000000001f;
									r3 = r1 / r2;
									r4 = r2 / r1;

									actChange = r3;
									if (SMP.UseAbsoluteValue && r4 > r3) // take the max value
										actChange = r4;

									break;
								}

							case ActDiffCalcType.Advanced:
								{
									throw new InvalidOperationException("SarMapCalcType.Advanced");
								}

							default:
								throw new InvalidOperationException("SarMapCalcType: " + (int)SMP.ActDiffCalcType);
						}

					if (SMP.UseAbsoluteValue && SMP.ActDiffCalcType != ActDiffCalcType.Ratio)
						actChange = Math.Abs(actChange);

					double coef = actChange / denom;

					if (pd.Count < SMP.MaxPairCount)  // just add this pair to end
					{
						pdi = pd.Count;
						pd.Add(new PairData());
					}

					else
					{ // see if this value is greater than anything in list
						if (minCoef < 0)
						{ // find element with minimum coef
							minCoef = 0;
							for (i3 = 1; i3 < pd.Count; i3++)
							{
								if (pd[i3].Coef < pd[minCoef].Coef)
									minCoef = i3;
							}
						}
						if (coef <= pd[minCoef].Coef) continue; // if this one better?

						//if (ds1 != ds2)
						//{ // be sure not a duplicate of what we have in list
						//	for (i3 = 0; i3 < pd.Count; i3++)
						//	{ // check for pair in list already
						//		if ((di == pd[i3].CD1 && di2 == pd[i3].CD2) ||
						//				(di == pd[i3].CD2 && di2 == pd[i3].CD1)) break;
						//	}
						//	if (i3 < pd.Count) continue; // continue to next pair if found
						//}

						pdi = minCoef; // replace this item
						minCoef = -1; // reset to get new minimum next time
					}

					// Save data for the pair

					pd[pdi].CD1 = cd1;
					pd[pdi].CD2 = cd2;
					pd[pdi].Sim = sim;
					pd[pdi].ActChange = actChange;
					pd[pdi].Coef = coef;
				}
			}

			// Build the list of pair Vos

			int voLen = qt.SetSimpleVoPositions();

			PairData pdItem;
			for (pdi = 1; pdi < pd.Count; pdi++) // sort from max to min coef value
			{
				pdItem = pd[pdi];
				for (pdi2 = pdi - 1; pdi2 >= 0; pdi2--)
				{
					if (pdItem.Coef < pd[pdi2].Coef) break;
					pd[pdi2 + 1] = pd[pdi2];
				}
				pd[pdi2 + 1] = pdItem;
			}

			for (pdi = 0; pdi < pd.Count; pdi++)
			{
				pdItem = pd[pdi];
				cd1 = pdItem.CD1;
				cd2 = pdItem.CD2;

				object[] vo = new object[voLen];

				VoArray.SetVo(qt, "PAIR_ID", vo, new NumberMx(pdi + 1));
				VoArray.SetVo(qt, "COMPOUND1", vo, new StringMx(cd1.Cid));
				VoArray.SetVo(qt, "STRUCTURE1", vo, cd1.Structure);
				VoArray.SetVo(qt, "ACTIVITY1", vo, new NumberMx(cd1.Activity));

				VoArray.SetVo(qt, "COMPOUND2", vo, new StringMx(cd2.Cid));
				VoArray.SetVo(qt, "STRUCTURE2", vo, cd2.Structure);
				VoArray.SetVo(qt, "ACTIVITY2", vo, new NumberMx(cd2.Activity));

				VoArray.SetVo(qt, "SIMILARITY", vo, new NumberMx(pdItem.Sim));
				VoArray.SetVo(qt, "ACTIVITY_DIFF", vo, new NumberMx(pdItem.ActChange));
				VoArray.SetVo(qt, "ACT_SIM_COEF", vo, new NumberMx(pdItem.Coef));

				VoList.Add(vo);
			}

			VoListPos = -1; // init list position
			return;
		}
Exemple #4
0
        /// <summary>
        /// Join an unpivoted target-summarized table to itself
        /// </summary>

        public QueryManager BuildTargetTargetData(
            QueryManager qm2)
        {
            QueryTable        qt, qt2;
            DataRowMx         dr, dr2;
            DataRowAttributes dra, dra2;
            string            cid = "", cid2 = "", currentCid, ttKey;
            int rti, rfi;

            qt = Query.GetQueryTableByNameWithException(MultiDbAssayDataNames.BaseTableName);                   // source table

            UnpivotedAssayResultFieldPositionMap voMap = UnpivotedAssayResultFieldPositionMap.NewOriginalMap(); // used for fast indexing of value by col name

            voMap.InitializeFromQueryTableVoPositions(qt, 0);

            if (qm2 == null || Math.Abs(1) == 1)             // need to create query manager? (needs fixup)
            {
                qm2 = new QueryManager();
                qm2 = InitializeSubqueryQm(MultiDbAssayDataNames.TargetTargetUnpivotedTableName);
            }

            Query q2 = qm2.Query;

            qt2 = q2.GetQueryTableByNameWithException(MultiDbAssayDataNames.TargetTargetUnpivotedTableName);

            // Join rows & store in DataSet

            qm2.DataTable.Clear();

            Dictionary <string, int>            ttDict = new Dictionary <string, int>(); // maps target pair to row
            Dictionary <string, List <string> > cidTargetDict = new Dictionary <string, List <string> >();

            for (int dri = 0; dri < DataTableMx.Rows.Count; dri++)
            {             // get list of targets for each cid
                dr  = DataTableMx.Rows[dri];
                cid = dr[KeyValueVoPos] as string;
                dra = GetRowAttributes(dr);
                if (dra != null && dra.Filtered)
                {
                    continue;
                }
                object[] vo = dr.ItemArrayRef;

                if (!cidTargetDict.ContainsKey(cid))
                {
                    cidTargetDict[cid] = new List <string>();
                }

                string target = AssayAttributes.GetStringVo(vo, voMap.TargetSymbol.Voi);
                cidTargetDict[cid].Add(target);
            }

            foreach (string cid0 in cidTargetDict.Keys)
            {             // sum count for pair of targets
                List <string> targets = cidTargetDict[cid0];
                for (int t1i = 0; t1i < targets.Count; t1i++)
                {
                    string t1 = targets[t1i];
                    for (int t2i = t1i; t2i < targets.Count; t2i++)
                    {
                        string t2 = targets[t2i];
                        if (Lex.Lt(t1, t2))
                        {
                            ttKey = t1 + '\t' + t2;
                        }
                        else
                        {
                            ttKey = t2 + '\t' + t1;
                        }

                        if (!ttDict.ContainsKey(ttKey))
                        {
                            ttDict[ttKey] = 0;
                        }
                        ttDict[ttKey]++;
                    }
                }
            }

            foreach (string ttkey0 in ttDict.Keys)
            {             // build data rows
                string[] t1t2     = ttkey0.Split('\t');
                string   t1       = t1t2[0];
                string   t2       = t1t2[1];
                int      cpdCount = ttDict[ttkey0];

                AddRow(qm2.DataTable, t1, t2, cpdCount);
                if (t2 != t1)
                {
                    AddRow(qm2.DataTable, t2, t1, cpdCount);
                }
            }

            qm2.DataTableManager.InitializeRowAttributes();

            return(qm2);
        }
Exemple #5
0
        /// <summary>
        /// Read in assay attribute information
        /// </summary>
        /// <returns></returns>

        public static AssayDict ReadAssayAttributesTable()
        {
            int       t0 = TimeOfDay.Milliseconds();
            MetaTable mt = MetaTableCollection.Get(AssayAttrsTableName);

            if (mt == null)
            {
                throw new Exception("Can't get table " + AssayAttrsTableName);
            }

            UnpivotedAssayResultFieldPositionMap voMap = new UnpivotedAssayResultFieldPositionMap();             // used for fast indexing of value by col name
            string fList = "";

            for (int mci = 0; mci < mt.MetaColumns.Count; mci++)
            {
                string colMap = mt.MetaColumns[mci].ColumnMap;
                voMap.TrySetVoi(colMap, mci);
                if (fList != "")
                {
                    fList += ", ";
                }
                fList += colMap;
            }

            string sql =
                "select " + fList + " " +
                "from mbs_owner.cmn_assy_atrbts " +
                "order by lower(gene_fmly), lower(gene_symbl), lower(assy_nm)";

//			sql = AdjustAssayAttrsTableName(sql);

            DbCommandMx dao = new DbCommandMx();

            dao.Prepare(sql);
            dao.ExecuteReader();

            AssayDict dict = new AssayDict();

            int readCnt = 0;

            while (true)
            {
                if (!dao.Read())
                {
                    break;
                }
                //if (readCnt > 100) break; // debug !!!
                readCnt++;
                object[] vo = new object[mt.MetaColumns.Count];
                for (int mci = 0; mci < mt.MetaColumns.Count; mci++)
                {
                    vo[mci] = dao.GetObject(mci);
                }

                AssayAttributes row = AssayAttributes.FromValueObjectNew(vo, voMap);
                dict.AssayList.Add(row);
                dict.SetMaps(row);
            }

            dao.CloseReader();
            dao.Dispose();
            t0 = TimeOfDay.Milliseconds() - t0;
            return(dict);
        }
Exemple #6
0
/// <summary>
/// Get a primitive value from the in-memory Target Assay Results cache
/// </summary>
/// <param name="dri"></param>
/// <param name="rfld"></param>
/// <returns></returns>

        public object GetPrimitiveValueFromTarCache(
            int dri,
            ResultsField rfld)
        {
            AssayAttributes taa = null;
            BinaryReader    br;
            long            streamLength;
            string          s;
            int             rowNum;

            GetPrimitiveValueFromTarCacheCalls++;

            ushort aid = AssayId[dri];

            if (aid == LastAid)
            {
                taa = LastTaa;
            }
            else if (TargetAssayDict.AssayIdMap.ContainsKey(aid))             // link to associated target-assay attributes
            {
                LastAid = aid;
                LastTaa = taa = TargetAssayDict.AssayIdMap[aid];
            }

            int voi = rfld.VoPosition;

            if (voi < 0 || voi >= TarFieldPositionMap.VoiToTarColEnum.Length)
            {
                return(null);                                                                          // not in range
            }
            TarColEnum tarCol = TarFieldPositionMap.VoiToTarColEnum[voi];

            switch (tarCol)
            {
// Assay Attributes - Get from TargetAssayDictionary

            case TarColEnum.Id:
                if (taa != null)
                {
                    return(taa.Id);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.TargetSymbol:
                if (taa != null)
                {
                    return(taa.GeneSymbol);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.TargetId:
                if (taa != null)
                {
                    return(taa.GeneId);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.TargetDescription:
                if (taa != null)
                {
                    return(taa.GeneDescription);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.GeneFamily:
                if (taa != null)
                {
                    return(taa.GeneFamily);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.GeneFamilyTargetSymbol:                     // catenation of target family & gene symbol
                if (taa != null)
                {
                    return(taa.GeneFamilyTargetSymbol);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.AssayDatabase:
                if (taa != null)
                {
                    return(taa.AssayDatabase);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.AssayMetaTableName:                     // name of the metatable containing the data
                if (taa != null)
                {
                    return(taa.AssayMetaTableName);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.AssayName:
                if (taa != null)
                {
                    return(taa.AssayName);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.AssayNameSx:                     // contains name & link when deserialized from StringEx
                if (taa != null)
                {
                    return(taa.AssayName);                                     // todo: add link
                }
                else
                {
                    return(null);
                }

            case TarColEnum.AssayDesc:
                if (taa != null)
                {
                    return(taa.AssayDesc);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.AssayLocation:
                if (taa != null)
                {
                    return(taa.AssaySource);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.AssayType:
                if (taa != null)
                {
                    return(taa.AssayType);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.AssayMode:
                if (taa != null)
                {
                    return(taa.AssayMode);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.AssayStatus:
                if (taa != null)
                {
                    return(taa.AssayStatus);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.AssayGeneCount:
                if (taa != null)
                {
                    return(taa.GeneCount);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.ResultName:                     // result label
                if (taa != null)
                {
                    return(taa.ResultName);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.ResultTypeIdTxt:                     // text form of result type code
                if (taa != null)
                {
                    return(taa.ResultTypeIdTxt);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.ResultTypeId2:                     // numeric form of result type code:
                if (taa != null)
                {
                    return(taa.ResultTypeId2);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.ResultTypeConcType:                     // SP or CRC
                if (taa != null)
                {
                    return(taa.ResultTypeConcType);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.TopLevelResult:                     // Y if this is a CRC or if a SP with no associated CRC
                if (taa != null)
                {
                    return(taa.TopLevelResult);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.Remapped:
                if (taa != null)
                {
                    return(taa.Remapped);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.Multiplexed:
                if (taa != null)
                {
                    return(taa.Multiplexed);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.Reviewed:
                if (taa != null)
                {
                    return(taa.Reviewed);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.ProfilingAssay:
                if (taa != null)
                {
                    return(taa.ProfilingAssay);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.CompoundsAssayed:
                if (taa != null)
                {
                    return(taa.CompoundsAssayed);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.ResultCount:
                if (taa != null)
                {
                    return(taa.ResultCount);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.AssayUpdateDate:                     // date of most recent assay result
                if (taa != null)
                {
                    return(taa.AssayUpdateDate);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.AssociationSource:                     // ASSAY2EG
                if (taa != null)
                {
                    return(taa.AssociationSource);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.AssociationConflict:                     // conflicting source & it's assignment
                if (taa != null)
                {
                    return(taa.AssociationConflict);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.TargetMapX:
                if (taa != null)
                {
                    return(taa.TargetMapX);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.TargetMapY:
                if (taa != null)
                {
                    return(taa.TargetMapY);
                }
                else
                {
                    return(null);
                }

            case TarColEnum.TargetMapZ:
                if (taa != null)
                {
                    return(taa.TargetMapZ);
                }
                else
                {
                    return(null);
                }

// Results - Get from in-memory arrays, one per table column

            case TarColEnum.CompoundId:
                if (CompoundId == null)
                {
                    CompoundId = ReadIntArray("CompoundId.bin");
                }
                return(CompoundId[dri]);

            case TarColEnum.AssayId2:                     // numeric form of assay/table identifier
                return((int)AssayId[dri]);                // should already have data AssayId array

            case TarColEnum.AssayIdTxt:                   // text form of assay/table identifier
                return(AssayId.ToString());

            //case TarColEnum.ResultTypeIdTxt: // text form of result type code
            //  if (ResultTypeId == null)
            //    ResultTypeId = ReadUShortArray("ResultTypeId.bin");
            //  return ResultTypeId[dri].ToString(); // get from results data array

            //case TarColEnum.ResultTypeIdNbr: // numeric form of result type identifier
            //  if (ResultTypeId == null)
            //    ResultTypeId = ReadUShortArray("ResultTypeId.bin");
            //  return (int)ResultTypeId[dri]; // get from results data array

            case TarColEnum.ActivityBin:
                if (ActivityBin == null)
                {
                    ActivityBin = ReadByteArray("ActivityBin.bin");
                }
                return((int)ActivityBin[dri]);

            case TarColEnum.ResultValue:                     // just return numeric value
                if (ResultNumericValue == null)
                {
                    ResultNumericValue = ReadSingleArray("ResultNumericValue.bin");
                }
                return(ResultNumericValue[dri]);

            case TarColEnum.ResultQualifier:
                if (ResultQualifier == null)
                {
                    ResultQualifier = ReadByteArray("ResultQualifier.bin");
                }

                if (ResultQualifier[dri] != ' ')
                {
                    return(ResultQualifier[dri].ToString());
                }
                else
                {
                    return("");
                }

            case TarColEnum.ResultNumericValue:
                if (ResultNumericValue == null)
                {
                    ResultNumericValue = ReadSingleArray("ResultNumericValue.bin");
                }
                return(ResultNumericValue[dri]);

            case TarColEnum.ResultTextValue:
                if (ResultTextValue == null)
                {
                    ResultTextValue = new Dictionary <int, string>();
                    br           = OpenBinaryReader("ResultTextValue.bin");
                    streamLength = br.BaseStream.Length;
                    while (true)
                    {
                        if (br.BaseStream.Position >= streamLength - 1)
                        {
                            break;
                        }
                        rowNum = br.ReadInt32();
                        s      = br.ReadString();
                        ResultTextValue[rowNum] = s;
                    }
                    br.Close();
                }

                if (ResultTextValue.ContainsKey(dri))
                {
                    return(ResultTextValue[dri]);
                }
                else
                {
                    return("");
                }

            case TarColEnum.NValue:
                return(0);

            case TarColEnum.NValueTested:
                return(0);

            case TarColEnum.StdDev:
                return(0);

            case TarColEnum.Units:
                if (Units == null)
                {
                    Units        = new Dictionary <int, string>();
                    br           = OpenBinaryReader("Units.bin");
                    streamLength = br.BaseStream.Length;
                    while (true)
                    {
                        if (br.BaseStream.Position >= streamLength - 1)
                        {
                            break;
                        }
                        rowNum        = br.ReadInt32();
                        s             = br.ReadString();
                        Units[rowNum] = s;
                    }
                    br.Close();
                }

                if (ResultTextValue.ContainsKey(dri))
                {
                    return(ResultTextValue[dri]);
                }
                else
                {
                    return("");
                }

            case TarColEnum.Conc:
                if (Conc == null)
                {
                    ReadConcData();
                }

                if (Conc.ContainsKey(dri))
                {
                    return(Conc[dri]);
                }
                else
                {
                    return(0);
                }

            case TarColEnum.ConcUnits:
                if (ConcUnits == null)
                {
                    ReadConcData();
                }

                if (ConcUnits.ContainsKey(dri))
                {
                    return(ConcUnits[dri]);
                }
                else
                {
                    return("");
                }

            case TarColEnum.RunDate:
                if (RunDate == null)
                {
                    RunDate = ReadUShortArray("RunDate.bin");
                }
                ushort   days = RunDate[dri];
                DateTime dt   = BaseDate.AddDays(days);
                return(dt);

            case TarColEnum.ResultDetail:
                return("");

            default:
                throw new Exception("Unexpected TarColEnum: " + tarCol);
            }
        }