/// <summary> /// Set vo with specified QueryColumn name /// </summary> /// <param name="qt"></param> /// <param name="colName"></param> /// <param name="vo"></param> /// <param name="voi"></param> /// <param name="value"></param> /// <param name="withException"></param> public static void SetVo( QueryTable qt, string colName, object[] vo, object value) { bool withException = false; // always false for now QueryColumn qc; if (withException) { qc = qt.GetQueryColumnByNameWithException(colName); } else { qc = qt.GetQueryColumnByName(colName); if (qc == null) { return; } } int voPos = qc.VoPosition; SetVo(vo, voPos, value); return; }
/// <summary> /// Set QueryTable criteria values from TargetSummaryOptions /// </summary> /// <param name="TargetSumTable"></param> public bool SetInQueryTable( QueryTable targetSumTable) { QueryTable qt = targetSumTable; if (qt == null) { return(false); } Query q = qt.Query; QueryColumn qc = qt.GetQueryColumnByNameWithException(MultiDbAssayDataNames.MultiDbViewOptions); if (qc == null) { return(false); } string tsoString = Serialize(); qc.Criteria = qc.MetaColumn.Name + " = " + Lex.AddSingleQuotes(tsoString); qc.CriteriaDisplay = "Edit..."; qt.KeyQueryColumn.Criteria = CidCriteria; // keep in variable criteria form SetListCriteriaForCol(qt, MultiDbAssayDataNames.GeneSymbol, TargetList); SetListCriteriaForCol(qt, MultiDbAssayDataNames.GeneFamily, GeneFamilies); return(true); }
/// <summary> /// General table / gene symbol query builder /// </summary> /// <param name="tableName"></param> /// <param name="geneSymbol"></param> /// <returns></returns> public static Query BuildTargetAssayQuery( string tableName, string geneSymbol) { Query q = new Query(); MetaTable mt = MetaTableCollection.GetWithException(tableName); QueryTable qt = new QueryTable(q, mt); QueryColumn qc = qt.GetQueryColumnByNameWithException(MultiDbAssayDataNames.GeneSymbol); qc.Criteria = MultiDbAssayDataNames.GeneSymbol + " = " + Lex.AddSingleQuotes(geneSymbol); qc.CriteriaDisplay = "= " + geneSymbol; if (qc.IsKey) // if key column store for that as well { q.KeyCriteria = " = " + Lex.AddSingleQuotes(geneSymbol); q.KeyCriteriaDisplay = "= " + geneSymbol; } //qc = qt.GetQueryColumnByNameWithException("top_lvl_rslt"); //qc.Criteria = "top_lvl_rslt = 'Y'"; // only top level results //qc.CriteriaDisplay = "= Y"; return(q); }
/// <summary> /// Initialize the chart /// </summary> DialogResult InitializeChart() { Qm1 = View.Qm; // assume chart initially pointing to root query manager DialogResult dr = Qm1.DataTableManager.CompleteRetrieval(); if (dr == DialogResult.Cancel) { return(dr); } Qm2 = null; // nothing for new query yet SummarizeDataUsingFormParameters(); // get initial summarized results & setup Qm2 View.BaseQuery = Qm2.Query; // link chart to new query & manager QueryTable qt = Qm2.Query.Tables[0]; // Setup for unpivoted results if (Qm2.DataTableManager.UnpivotedResults) { QueryColumn cidQc = qt.GetQueryColumnByNameWithException("CORP_ID"); View.YAxisMx.QueryColumn = cidQc; QueryColumn assyNameQc = qt.GetQueryColumnByNameWithException("ASSY_NM"); View.XAxisMx.QueryColumn = assyNameQc; View.XAxisMx.LabelAngle = -90; // vertical QueryColumn rsltQc = qt.GetQueryColumnByNameWithException("RSLT_VAL"); View.MarkerColor.QueryColumn = rsltQc; View.MarkerColor.FieldValueContainsColor = true; View.UseExistingCondFormatting = true; View.PivotedData = false; } // Setup for pivoted results else { throw new NotImplementedException(); // todo } View.ConfigureRenderingControl(); // render return(DialogResult.OK); }
/// <summary> /// Build summarization detail query /// </summary> /// <param name="mt"></param> /// <param name="mc"></param> /// <param name="level"></param> /// <param name="linkInfo"></param> /// <returns></returns> public override Query GetDrilldownDetailQuery( MetaTable mt, MetaColumn mc, int level, string linkInfo) { QueryTable qt, qt2; MetaTable mt2; QueryColumn qc, qc2; MetaColumn mc2; string resultIdColName = ""; Query q = new Query(); // Summarized MdbAssay table drilling down to unsummarized MdbAssay table if (UnpivotedAssayResult.IsSummarizedMdbAssayTable(mt.Name)) { if (UnpivotedAssayResult.IsCombinedMdbAssayTable(mt.Name)) // multiple tables { mt2 = MetaTableCollection.GetWithException(MultiDbAssayDataNames.CombinedNonSumTableName); resultIdColName = MultiDbAssayDataNames.SumResultId; } else // base unpivoted or pivoted by target { mt2 = MetaTableCollection.GetWithException(MultiDbAssayDataNames.BaseNonSumTableName); resultIdColName = MultiDbAssayDataNames.BaseSumResultId; } qt = new QueryTable(q, mt2); qc = qt.GetQueryColumnByNameWithException(resultIdColName); qc.Criteria = resultIdColName + " = " + linkInfo; return(q); } // Drilling down from old all_bioassay_unpivoted to specific source data else if (Lex.Eq(mt.Name, UnpivotedAssayView.UnsummarizedMetaTableName)) { // ResultId is formatted by the source with the first item being the source name Match m = Regex.Match(linkInfo, @"(.*),(.*),(.*)", RegexOptions.IgnoreCase | RegexOptions.RightToLeft); string mtName = m.Groups[1].Value; string mcId = m.Groups[2].Value; string linkInfo2 = m.Groups[3].Value; mt2 = MetaTableCollection.GetWithException(mtName); IMetaBroker imb = MetaBrokerUtil.GlobalBrokers[(int)mt2.MetaBrokerType]; q = imb.GetDrilldownDetailQuery(mt2, mcId, level, linkInfo2); return(q); } else { throw new ArgumentException("Invalid tableName: " + mt.Name); } }
/// <summary> /// Get query to select all data. /// It consists of a single QueryTable with the CID and key table name /// This keeps the initial query sent to the client small which helps /// response time for users not on the same local network as the server. /// </summary> /// <param name="keyMtName"></param> /// <param name="cn"></param> /// <returns></returns> public static Query GetSelectAllDataQuery( string keyMtName, string cn) { MetaTable mt = MetaTableCollection.GetWithException(MetaTable.AllDataQueryTable); Query q = new Query(); QueryTable qt = new QueryTable(mt); q.AddQueryTable(qt); MetaTable keyMt = null; if (Lex.IsDefined(keyMtName)) { keyMt = MetaTableCollection.Get(keyMtName); } if (keyMt != null && keyMt.Root.IsUserDatabaseStructureTable) // if root metatable is user database then normalize based on key { keyMt = keyMt.Root; // be sure we have root cn = CompoundId.Normalize(cn, keyMt); } else { cn = CompoundId.Normalize(cn); keyMt = CompoundId.GetRootMetaTableFromCid(cn, keyMt); keyMt = keyMt.Root; // be sure we have root (may not be structure table) } if (keyMt == null) { throw new Exception("Failed to identify key MetaTable"); } q.KeyCriteria = q.KeyCriteriaDisplay = " = " + cn; QueryColumn qc = qt.GetQueryColumnByNameWithException("root_table"); qc.Criteria = qc.CriteriaDisplay = qc.MetaColumn.Name + " = " + keyMt.Name; return(q); }
/// <summary> /// Create a new pivoted DataTable in the specified format from the existing unpivoted DataTable /// </summary> /// <param name="rows"></param> /// <param name="pivotType"></param> /// <param name="qt"></param> /// <param name="newQuery"></param> /// <param name="ResultKeys"></param> void CreatePivotedTable( DataSet rows, TargetAssayPivotFormat pivotType, QueryTable qt, Query newQuery, List <string> ResultKeys) { #if false UnpivotedAssayResult rr; QueryColumn qc, qc2; MetaColumn mc, mc2; string pivotValue; int row, pivotPos, pos, colsPerPivotValue, qci, mci, pi, ri; bool pivotByTarget = false, pivotByFamily = false, orderByFamily = false; if (pivotType == TargetAssayPivotFormat. { pivotByTarget = true; orderByFamily = // if gene family selected then order by it first before target qt.GetQueryColumnByNameWithException("gene_fmly").Selected; }
/// <summary> /// Tools menu item click /// Create a standalone SasMap analysis based on the current query and then run it /// </summary> /// <param name="args"></param> /// <returns></returns> public string Run( string args) { QueryTable qt; MetaTable mt; QueryColumn qc; int qid = SS.I.ServicesIniFile.ReadInt("SasMapToolModelQuery", 888070); if (qid < 0) { throw new Exception("SasMapToolModelQuery not defined"); } Query q = QbUtil.ReadQuery(qid); if (q == null) { throw new Exception("Failed to read SasMapToolModelQuery: " + qid); } q.UserObject = new UserObject(UserObjectType.Query); // treat this as a new query QueryTable sasQt = q.GetQueryTableByNameWithException(SasMapParms.MetaTableName); SasQc = sasQt.GetQueryColumnByNameWithException(SasMapParms.ParametersMetaColumnName); BaseQuery = GetBaseQuery(); if (BaseQuery == null || BaseQuery.Tables.Count == 0) { MessageBoxMx.ShowError("The current query must contain one or more data tables before a SasMap can be defined on the query"); return(""); } Parms = LastParms; qc = BaseQuery?.GetFirstKeyColumn(); qc = qc.Clone(); qc.CopyCriteriaFromQueryKeyCriteria(BaseQuery); Parms.KeyCriteriaQc = qc; Parms.EndpointMc = BaseQuery?.GetFirstResultColumnByPriority(true)?.MetaColumn; // pick the highest priority result column as the default endpoint QbUtil.SetMode(QueryMode.Build); // be sure in build mode DialogResult dr = Edit(); if (dr != DialogResult.OK) { return(""); } StoreParmsInQueryColumnCriteria(Parms, SasQc); // Add as a new query and run it AnalysisCount++; q.Name = "Structure-Activity Similarity Analysis " + AnalysisCount; q.SingleStepExecution = true; // Add the query to the QueryBuilder and run it DelayedCallback.Schedule( // add and run the query after exiting this dialog delegate(object state) { QbUtil.AddQuery(q); QbUtil.SetCurrentQueryInstance(q); // set to new query QbUtil.RenderQuery(); QueryExec.RunQuery(q, SS.I.DefaultQueryDest); }); Progress.Hide(); return(""); }
/// <summary> /// OK button clicked, process input /// </summary> /// <returns></returns> DialogResult ProcessInput() { int rgCount; // number of Rgroups List <Rgroup> rgList; // list of existing Rgroups bool[] rgExists; // entry = true if rgroup exists in core Rgroup rg; bool oneD, twoD; // matrix dimensionality List <string> keys = null; Dictionary <string, List <QualifiedNumber> > mElem; // matrix element dictionary List <RgroupSubstituent>[] rSubs; // substituents seen for each Rgroup Query q, q0, q2; QueryTable qt, qt2; QueryColumn qc, qc2; MetaTable mt, mt2; MetaColumn mc, mc2; DataTableMx dt; DataRowMx dr; DialogResult dlgRslt; string tok; int ri, rii, si, qti, qci, bi, bi2; // Get core structure & list of R-groups MoleculeMx core = new MoleculeMx(MoleculeFormat.Molfile, SQuery.MolfileString); if (core.AtomCount == 0) { MessageBoxMx.ShowError("A Core structure with R-groups must be defined"); return(DialogResult.None); } if (!Structure.Checked && !Smiles.Checked && !Formula.Checked && !Weight.Checked && !Index.Checked) { MessageBoxMx.ShowError("At least one substituent display format must be selected."); return(DialogResult.None); } mt = MetaTableCollection.GetWithException("Rgroup_Decomposition"); qt = new QueryTable(mt); qc = qt.GetQueryColumnByNameWithException("Core"); qc.MolString = core.GetMolfileString(); // put core structure into table criteria qc.CriteriaDisplay = "Substructure search (SSS)"; qc.Criteria = "CORE SSS SQUERY"; qc = qt.GetQueryColumnByNameWithException("R1_Structure"); if (ShowCoreStructure.Checked) { qc.Label = "R-group, Core\tChime=" + core.GetChimeString(); // reference core in query col header label qc.MetaColumn.Width = 25; } RgroupDecomposition.SetSelected(qt, "R1_Structure", Structure.Checked); // select for retrieval if checked RgroupDecomposition.SetSelected(qt, "R1_Smiles", Smiles.Checked); RgroupDecomposition.SetSelected(qt, "R1_Formula", Formula.Checked); RgroupDecomposition.SetSelected(qt, "R1_Weight", Weight.Checked); RgroupDecomposition.SetSelected(qt, "R1_SubstNo", Index.Checked); string terminateOption = "First mapping"; // terminate on first complete match qc = qt.GetQueryColumnByNameWithException("Terminate_Option"); qc.Criteria = qt.MetaTable.Name + " = " + Lex.AddSingleQuotes(terminateOption); qc.CriteriaDisplay = "= " + Lex.AddSingleQuotes(terminateOption); QueryTable rgdQt = qt; // keep a ref to it if (QbUtil.Query == null || QbUtil.Query.Tables.Count == 0) { MessageBoxMx.ShowError("No current query."); return(DialogResult.None); } q0 = QbUtil.Query; // original query this analysis is based on q = q0.Clone(); // make copy of source query we can modify q.SingleStepExecution = false; qti = 0; while (qti < q.Tables.Count) // deselect query columns that we don't want { qt = q.Tables[qti]; if (Lex.Eq(qt.MetaTable.Name, "Rgroup_Decomposition")) { // remove any rgroup decomp table qti++; continue; } mt = qt.MetaTable; if (mt.MultiPivot || // check for tables not allowed in underlying query mt.MetaBrokerType == MetaBrokerType.CalcField || // (called ShouldPresearchAndTransform previously) mt.MetaBrokerType == MetaBrokerType.MultiTable || mt.MetaBrokerType == MetaBrokerType.RgroupDecomp) { MessageBoxMx.ShowError("Multipivot/Rgroup table \"" + qt.ActiveLabel + "\" can't be included in an underlying Rgroup Matrix query"); return(DialogResult.None); } for (qci = 0; qci < qt.QueryColumns.Count; qci++) { qc = qt.QueryColumns[qci]; if (qc.MetaColumn == null) { continue; } switch (qc.MetaColumn.DataType) { case MetaColumnType.CompoundId: // keep only these case MetaColumnType.Integer: case MetaColumnType.Number: case MetaColumnType.QualifiedNo: case MetaColumnType.String: break; default: qc.Selected = false; break; } } qti++; } q.AddQueryTable(rgdQt); // Add Rgroup decom table to end of cloned source query Progress.Show("Retrieving data..."); try { dlgRslt = ToolHelper.ExecuteQuery(ref q, out keys); if (dlgRslt != DialogResult.OK) { return(dlgRslt); } } catch (Exception ex) { MessageBoxMx.ShowError("Error executing query:\r\n" + ex.Message); return(DialogResult.None); } if (keys == null || keys.Count == 0) { Progress.Hide(); MessageBoxMx.ShowError("No results were returned by the query."); return(DialogResult.None); } // Scan modified query to get list of rgroup indexes that are present rgExists = new bool[32]; rgList = new List <Rgroup>(); QueryTable rgQt = q.GetQueryTableByName("Rgroup_Decomposition"); foreach (QueryColumn qc0 in rgQt.QueryColumns) { mc = qc0.MetaColumn; if (!(mc.Name.StartsWith("R") && mc.Name.EndsWith("_STRUCTURE") && qc0.Selected)) { continue; // skip if not a selected Rgroup structure } int len = mc.Name.Length - ("R" + "_STRUCTURE").Length; tok = mc.Name.Substring(1, len); if (!int.TryParse(tok, out ri)) { continue; } rgExists[ri - 1] = true; rg = new Rgroup(); rg.RIndex = ri; rg.VoPos = qc0.VoPosition; rgList.Add(rg); } for (bi = 1; bi < rgList.Count; bi++) { // sort by increasing R index rg = rgList[bi]; for (bi2 = bi - 1; bi2 >= 0; bi2--) { if (rg.RIndex >= rgList[bi2].RIndex) { break; } rgList[bi2 + 1] = rgList[bi2]; } rgList[bi2 + 1] = rg; } rgCount = rgList.Count; twoD = TwoD.Checked; if (rgCount == 1) { twoD = false; // if only 1 rgroup can't do as 2d } oneD = !twoD; // Read data into mElem and rgroup substituents into rSubs. // Matrix mElem is keyed on [R1Smiles, R2Smiles,... RnSmiles, FieldName] for 1d and // [R1Smiles, R2Smiles,... FieldName, RnSmiles] for 2d QueryManager qm = q.QueryManager as QueryManager; DataTableManager dtm = qm.DataTableManager; dt = qm.DataTable; mElem = new Dictionary <string, List <QualifiedNumber> >(); // matrix element dictionary rSubs = new List <RgroupSubstituent> [32]; // list of substituents seen for each Rgroup for (rii = 0; rii < rgCount; rii++) // alloc substituent list for rgroup { rSubs[rii] = new List <RgroupSubstituent>(); } int rowCount = 0; while (true) { // scan data accumulating rgroup substituents and data values dr = dtm.FetchNextDataRow(); if (dr == null) { break; } rowCount++; string cid = dr[dtm.KeyValueVoPos] as string; string lastMapCid = "", rgroupKey = "", rgroupKeyLast = ""; int mapCount = 0; for (rii = 0; rii < rgCount; rii++) // for { MoleculeMx rSub = dr[rgList[rii].VoPos] as MoleculeMx; if (rSub == null || rSub.AtomCount == 0) { continue; } ri = rgList[rii].RIndex; // actual R index in query int subIdx = RgroupSubstituent.Get(rSub, rSubs[rii]); // if (ri == 1 && subIdx != 0) subIdx = subIdx; // debug if (subIdx < 0) { continue; } string rKey = "R" + ri.ToString() + "_" + (subIdx + 1).ToString(); if (oneD || rii < rgCount - 1) { if (rgroupKey != "") { rgroupKey += "\t"; } rgroupKey += rKey; } else { rgroupKeyLast = rKey; } lastMapCid = cid; mapCount++; } if (lastMapCid == cid) // add the data if compound has a mapping { AccumulateMatrixElements(mElem, q, dr, rgroupKey, rgroupKeyLast, cid); } if (Progress.IsTimeToUpdate) { Progress.Show("Retrieving data: " + StringMx.FormatIntegerWithCommas(rowCount) + " rows..."); } } if (rowCount == 0) { Progress.Hide(); MessageBoxMx.ShowError("No data rows retrieved"); return(DialogResult.None); } if (twoD && (rSubs[rgCount - 1] == null || rSubs[rgCount - 1].Count == 0)) { // if 2D be sure we have at least one substituent for the last Rgroup Progress.Hide(); MessageBoxMx.ShowError("No substituents found for R" + rgCount.ToString()); return(DialogResult.None); } // Create a MetaTable & DataTable for matrix results Progress.Show("Analyzing data..."); mt = new MetaTable(); // create output table MatrixCount++; mt.Name = "RGROUPMATRIX_" + MatrixCount; mt.Label = "R-group Matrix " + MatrixCount; mt.MetaBrokerType = MetaBrokerType.RgroupDecomp; mc = // use sequence for key mt.AddMetaColumn("RgroupMatrixId", "No.", MetaColumnType.Integer, ColumnSelectionEnum.Selected, 3); mc.ClickFunction = "None"; // avoid hyperlink on this key mc.IsKey = true; int maxLeftR = rgCount; if (twoD) { maxLeftR = rgCount - 1; } for (ri = 0; ri < maxLeftR; ri++) { string rStr = "R" + (ri + 1).ToString(); if (Structure.Checked) { mc = mt.AddMetaColumn(rStr + "Str", rStr, MetaColumnType.Structure, ColumnSelectionEnum.Selected, 12); if (ri == 0 && ShowCoreStructure.Checked) // include core structure above R1 if requested { string chimeString = MoleculeMx.MolfileStringToSmilesString(SQuery.MolfileString); mc.Label = "R1, Core\tChime=" + chimeString; mc.Width = 25; } } if (Smiles.Checked) { mc = mt.AddMetaColumn(rStr + "Smi", rStr + " Smiles", MetaColumnType.String, ColumnSelectionEnum.Selected, 12); } if (Formula.Checked) { mc = mt.AddMetaColumn(rStr + "Mf", rStr + " Formula", MetaColumnType.String, ColumnSelectionEnum.Selected, 8); } if (Weight.Checked) { mc = mt.AddMetaColumn(rStr + "MW", rStr + " Mol. Wt.", MetaColumnType.Number, ColumnSelectionEnum.Selected, 6, ColumnFormatEnum.Decimal, 2); } if (Index.Checked) { mc = mt.AddMetaColumn(rStr + "Index", rStr + " Subst. Idx.", MetaColumnType.Number, ColumnSelectionEnum.Selected, 4); mc.Format = ColumnFormatEnum.Decimal; } } mc = // add column to contain result type mt.AddMetaColumn("ResultType", "Result Type", MetaColumnType.String, ColumnSelectionEnum.Selected, 12); if (oneD) // add just 1 column to contain results { mc = mt.AddMetaColumn("Results", "Results", MetaColumnType.QualifiedNo, ColumnSelectionEnum.Selected, 12); mc.MetaBrokerType = MetaBrokerType.RgroupDecomp; // broker to do special col handling for cond formtting if (QbUtil.Query.UserObject.Id > 0) { mc.DetailsAvailable = true; } } else // add col for each substituent for last rgroup { string rStr = "R" + rgCount.ToString(); for (si = 0; si < rSubs[rgCount - 1].Count; si++) { string cName = rStr + "_" + (si + 1).ToString(); string cLabel = cName.Replace("_", "."); RgroupSubstituent rgs = rSubs[ri][si]; // get substituent info if (Structure.Checked) // include structure { cLabel += "\tChime=" + rgs.Struct.GetChimeString(); } else if (Smiles.Checked) { cLabel += " = " + rgs.Struct.GetSmilesString(); } else if (Formula.Checked) { cLabel += " = " + rgs.Struct.MolFormula; } else if (Weight.Checked) { cLabel += " = " + rgs.Struct.MolWeight; } else if (Index.Checked) { cLabel += " = " + (si + 1).ToString(); } mc = mt.AddMetaColumn(cName, cLabel, MetaColumnType.QualifiedNo, ColumnSelectionEnum.Selected, 12); mc.MetaBrokerType = MetaBrokerType.RgroupDecomp; if (QbUtil.Query.UserObject.Id > 0) { mc.DetailsAvailable = true; } } } MetaTableCollection.UpdateGlobally(mt); // add as a known metatable if (mElem.Count == 0) // be sure we have a matrix { Progress.Hide(); MessageBoxMx.ShowError("No matrix can be created because insufficient data was found."); return(DialogResult.None); } // Build the DataTable Progress.Show("Building data table..."); q2 = new Query(); // build single-table query to hold matrix qt2 = new QueryTable(q2, mt); dt = DataTableManager.BuildDataTable(q2); Dictionary <string, List <QualifiedNumber> > .KeyCollection kc = mElem.Keys; string[] rgKeys = new string[mElem.Count]; kc.CopyTo(rgKeys, 0); Array.Sort(rgKeys); string[] rgKey = null, lastRgKey = null; int rki = 0; for (rki = 0; rki < rgKeys.Length; rki++) { rgKey = rgKeys[rki].Split('\t'); int riTop = rgCount + 1; // all r substituents & field name on left if (twoD) { riTop = rgCount; } for (ri = 0; ri < riTop; ri++) // see if any changes in left side substituents or field name { if (lastRgKey == null || rgKey[ri] != lastRgKey[ri]) { break; } } if (ri < riTop || oneD) // if 2d then new row only if some change before last R { dr = dt.NewRow(); dt.Rows.Add(dr); dr[dtm.KeyValueVoPos + 1] = new NumberMx(dt.Rows.Count); // integer row key } if (!HideRepeatingSubstituents.Checked) { ri = 0; // start at first if not hiding } lastRgKey = rgKey; for (ri = ri; ri < riTop; ri++) // build row with these { string rgSub = rgKey[ri]; // get substituent id or table.column name if (rgSub == "") { continue; } if (ri < riTop - 1) { // output substituent and/or smiles string rStr = "R" + (ri + 1).ToString(); si = rgSub.IndexOf("_"); si = Int32.Parse(rgSub.Substring(si + 1)) - 1; // get substituent index RgroupSubstituent rgs = rSubs[ri][si]; // get substituent info if (Structure.Checked) { qc2 = qt2.GetQueryColumnByName(rStr + "Str"); dr[QcToDcName(qc2)] = rgs.Struct; } if (Smiles.Checked) { qc2 = qt2.GetQueryColumnByName(rStr + "Smi"); dr[QcToDcName(qc2)] = new StringMx(rgs.Struct.GetSmilesString()); } if (Formula.Checked) { qc2 = qt2.GetQueryColumnByName(rStr + "Mf"); dr[QcToDcName(qc2)] = new StringMx(rgs.Struct.MolFormula); } if (Weight.Checked) { qc2 = qt2.GetQueryColumnByName(rStr + "Mw"); dr[QcToDcName(qc2)] = new NumberMx(rgs.Struct.MolWeight); } if (Index.Checked) { qc2 = qt2.GetQueryColumnByName(rStr + "Index"); dr[QcToDcName(qc2)] = new NumberMx(si + 1); } } else // output field name { string[] sa = rgSub.Split('.'); // get field name qt = q.GetQueryTableByName(sa[0]); qc = qt.GetQueryColumnByName(sa[1]); string fieldName = qc.ActiveLabel; if (q0.Tables.Count >= 3) // qualify by table if 3 or more tables in original query { fieldName = qt.ActiveLabel + " - " + fieldName; } qc2 = qt2.GetQueryColumnByName("ResultType"); dr[QcToDcName(qc2)] = new StringMx(fieldName); } } // Output value string cName; if (oneD) { cName = "Results"; } else { cName = rgKey[rgCount]; // get key for this substituent (e.g. R2_1) } if (Lex.IsUndefined(cName)) { continue; // may be no substituent match } qc2 = qt2.GetQueryColumnByName(cName); QualifiedNumber qn = SummarizeData(mElem[rgKeys[rki]]); // get summarized value dr[QcToDcName(qc2)] = qn; } ToolHelper.DisplayData(q2, dt, true); UsageDao.LogEvent("RgroupMatrix"); Progress.Hide(); return(DialogResult.OK); }
/// <summary> /// Transform basic query to select all data for a compound number /// </summary> /// <param name="keyMt">Key metatable. If null then try to determine from key value</param> /// <param name="cn"></param> /// <returns></returns> public static Query TransformSelectAllDataQuery( Query originalQuery, QueryTable qt0, Query newQuery) { Query q2 = null; MetaTable mt; MetaColumn mc; QueryTable qt; QueryColumn qc; MetaTreeNode mtn, tn; qc = qt0.GetQueryColumnByNameWithException("root_table"); ParsedSingleCriteria psc = MqlUtil.ParseQueryColumnCriteria(qc); if (psc == null || Lex.IsUndefined(psc.Value)) { throw new UserQueryException("Root table not defined"); } string keyMtName = psc.Value; psc = MqlUtil.ParseSingleCriteria("cid " + originalQuery.KeyCriteria); if (psc == null || Lex.IsUndefined(psc.Value)) { throw new UserQueryException("Compound Id not defined"); } string cn = psc.Value; MetaTable keyMt = null; if (Lex.IsDefined(keyMtName)) { keyMt = MetaTableCollection.Get(keyMtName); } if (keyMt != null && keyMt.Root.IsUserDatabaseStructureTable) // if root metatable is user database then normalize based on key { keyMt = keyMt.Root; // be sure we have root cn = CompoundId.Normalize(cn, keyMt); } else { cn = CompoundId.Normalize(cn); keyMt = CompoundId.GetRootMetaTableFromCid(cn, keyMt); keyMt = keyMt.Root; // be sure we have root (may not be structure table) } if (keyMt == null) { throw new Exception("Failed to identify key MetaTable"); } string allTableName = keyMt.Name + "_AllData"; // see if specific all-data tree node mtn = MetaTree.GetNode(allTableName); if (mtn == null) // no special "_AllData" node, lookup in menu { foreach (MetaTreeNode parent in MetaTree.Nodes.Values) { foreach (MetaTreeNode child in parent.Nodes) { if (Lex.Eq(child.Target, keyMt.Name)) { mtn = parent; break; } } } IUserObjectTree iuot = InterfaceRefs.IUserObjectTree; if (mtn == null && keyMt.IsUserDatabaseStructureTable && iuot != null) // see if user structure table & db { int userObjId = UserObject.ParseObjectIdFromInternalName(keyMt.Name); string nodeItemId = "ANNOTATION_" + userObjId; MetaTreeNode childMtn = iuot.GetUserObjectNodeBytarget(nodeItemId); if (childMtn != null && childMtn.Parent.Type == MetaTreeNodeType.Database) { mtn = childMtn.Parent; } } } if (mtn == null) { return(null); } Query q = newQuery; for (int i1 = 0; i1 < mtn.Nodes.Count; i1++) { tn = (MetaTreeNode)mtn.Nodes[i1]; if (!tn.IsDataTableType) { continue; } mt = MetaTableCollection.Get(tn.Target); if (mt == null) { continue; } if (mt.Root.Name != keyMt.Name) { continue; // must have same root } if (mt.MultiPivot && !mt.UseSummarizedData && mt.SummarizedExists) { MetaTable mt2 = MetaTableCollection.Get(mt.Name + MetaTable.SummarySuffix); if (mt2 != null) { mt = mt2; } } //if (mt.RemapForRetrieval && mt.SummarizedExists) mt.UseSummarizedData = true; // get summarized multipivot data (not good, permanently changes the metatable) qt = new QueryTable(mt); // if (Lex.Eq(mt.Name, "all_star_pivoted") || Lex.Eq(mt.Name, "all_annotation_pivoted")) mt = mt // debug; if (qt.SelectedCount > 0) // be sure something is selected { q.AddQueryTable(qt); } } // See if a model query exists & use it or append to what we have already string fileName = ServicesDirs.ModelQueriesDir + @"\" + allTableName + ".qry"; if (!ServerFile.GetLastWriteTime(fileName).Equals(DateTime.MinValue)) // model query file exist? { try { string query2String = FileUtil.ReadFile(fileName); q2 = Query.Deserialize(query2String); q.MergeSubqueries(q2); // just use subquery } catch (Exception ex) { ex = ex; } } q.SetupQueryPagesAndViews(ResultsViewType.HtmlTable); // be sure we have a default page & HTML view // Set key criteria q.KeyCriteria = " = " + cn; // Review tables (debug) //int tCnt = q.Tables.Count; //string tls = q.TableListString; //q.Tables.RemoveRange(23, 1); //q.Tables.RemoveRange(27, q.Tables.Count - 27); //q.Tables.RemoveRange(1, 25); // Get list of any inaccessible tables & remove from query q.InaccessableData = CheckDataSourceAccessibility(q); if (q.InaccessableData != null) { foreach (string schema in q.InaccessableData.Keys) { foreach (string tName in q.InaccessableData[schema]) { qt = q.GetQueryTableByName(tName); if (qt != null && !qt.MetaTable.IsRootTable && q.Tables.Contains(qt)) { q.RemoveQueryTable(qt); } } } //ShowUnavailableDataMessage(q); q.InaccessableData = null; } UsageDao.LogEvent("QueryAllData", ""); //string mql = MqlUtil.ConvertQueryToMql(q); // debug return(q); }
/// <summary> /// Handle a click on a link to a 3D structure & open in Vida /// If clicked from an XtraGrid give the user a range of options for the data /// to be exported. If clicked from a HTML page give limited options. /// </summary> /// <param name="mtName"></param> /// <param name="mcName"></param> /// <param name="url"></param> public void ClickFunction( string mtName, string mcName, string url) { ResultsFormatter fmtr = null; ResultsField rfld = null; //DataRowMx dr; StringMx sx; StreamWriter sw; int markedCount = 0; string densityMapUrl = null; string target = null; _currentMetaTable = mtName; _currentPdbColumnName = mcName; IncludeElectronDensityPyMol.Checked = false; MetaTable mt = MetaTableCollection.Get(mtName); if (mt == null) { return; } MetaColumn mc = mt.GetMetaColumnByName(mcName); if (mc == null) { return; } _xRay2Request = Lex.Contains(mtName, "XRay2"); // newer XRay2 database table? _urlFileName = Path.GetFileName(url); // extract file name from url QueryManager qm = ClickFunctions.CurrentClickQueryManager; if (qm != null) { Query q = qm.Query; ResultsFormat rf = qm.ResultsFormat; fmtr = qm.ResultsFormatter; rfld = rf.GetResultsField(mc); if (rfld == null) { return; } QueryTable qt = q.GetQueryTableByNameWithException(mtName); _qcProtein = qt.GetQueryColumnByNameWithException(mcName); if (_xRay2Request) // newer XRay2 database { string mapUrl = ""; if (mcName == "ALIGNED_SPLIT_COMPLEX_URL") { mapUrl = "ALIGNED_SPLIT_MAP_URL"; } else if (mcName == "ALIGNED_FULL_COMPLEX_URL") { mapUrl = "ALIGNED_FULL_MAP_URL"; } else if (mcName == "ORIGINAL_PDB_URL") { mapUrl = "ORIGINAL_MAP_URL"; } //{ mapUrl = "ORIGINAL_MAP_URL"; } _qcDensity = qt.GetQueryColumnByName(mapUrl); //("ALIGNED_SPLIT_MAP_URL"); if (_qcDensity != null && !_qcDensity.Selected) { _qcDensity = null; } _qcTarget = qt.GetQueryColumnByName("primary_gene_name_alias"); if (_qcTarget != null && !_qcTarget.Selected) { _qcTarget = null; } } if (_qcDensity != null) { // if there is a density map url located in the density column, enable the PyMol CheckEdit DataRowMx dr = qm.DataTable.Rows[qm.MoleculeGrid.LastMouseDownCellInfo.DataRowIndex]; densityMapUrl = Lex.ToString(dr[_qcDensity.VoPosition]); } else { // user did not select the map column, try to retrieve the XRay1 or Xray2 density map url fromt he metatable densityMapUrl = _xRay2Request ? GetXray2DensityMapUrl(url, qt.MetaTable.Name, mcName) : GetXray1DensityMapUrl(url, qt.MetaTable.Name); } // default checkbox to false so user does not load electron density maps everytime, since these take // extra time to load. IncludeElectronDensityPyMol.Checked = false; IncludeElectronDensityPyMol.Enabled = !string.IsNullOrEmpty(densityMapUrl); if (_qcProtein == null && _qcDensity == null) { throw new Exception("Neither the PDB nor the MAP column is selected in the query"); } markedCount = fmtr.MarkedRowCount; int unmarkedCount = fmtr.UnmarkedRowCount; if (markedCount == 0 || unmarkedCount == 0) // if no specific selection assume user wants single structure { ExportSingle.Checked = true; FileName.Text = _urlFileName; } else { ExportMarked.Checked = true; // assume wants marked structures } ExportMarked.Enabled = true; } else // simple setup for click from HTML display { ExportSingle.Checked = true; ExportMarked.Enabled = false; FileName.Text = _urlFileName; densityMapUrl = GetXray2DensityMapUrl(url, _currentMetaTable, mcName); target = GetTarget(url, _currentMetaTable, mcName); ////IncludeProtein.Enabled = IncludeElectronDensityEnabled = false; ////if (Lex.Eq(mcName, "bsl_xray_cmplx_url")) //// IncludeProtein.Checked = true; ////else if (Lex.Eq(mcName, "bsl_xray_edensity_url")) //// IncludeElectronDensityChecked = true; } if (mcName == "ALIGNED_SPLIT_MAP_URL" || mcName == "ALIGNED_FULL_MAP_URL" || mcName == "ORIGINAL_MAP_URL") // not viewable fileds { DisablePymol(); DisableMoe(); ExportToFile.Enabled = ExportToFile.Checked = true; } else if (mcName == "ALIGNED_FULL_COMPLEX_URL" || mcName == "ORIGINAL_PDB_URL" || mcName == "ALIGNED_SPLIT_COMPLEX_URL") // viewable by PyMol { EnableMoe(); EnablePymol(); ExportToFile.Enabled = true; ExportToFile.Checked = false; } else //everything else should be viewable by MOE { EnableMoe(); DisablePymol(); ExportToFile.Enabled = true; ExportToFile.Checked = false; } DialogResult dlgRslt = ShowDialog(SessionManager.ActiveForm); if (dlgRslt == DialogResult.Cancel) { return; } bool exportSingle = ExportSingle.Checked; bool exportMarked = !exportSingle; if (!IncludeElectronDensityPyMol.Checked) { densityMapUrl = null; } if (exportMarked) // see if reasonable count if exporting marked rows { string msg; if (markedCount == 0) { msg = "No rows have been marked for export"; MessageBoxMx.Show(msg, "Mobius", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } if (markedCount > 10) { msg = markedCount + " structures have been selected out export.\n" + "Are you sure these are the structures you want to export?"; dlgRslt = MessageBoxMx.Show(msg, "Mobius", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question); if (dlgRslt != DialogResult.Yes) { return; } } } // Export to PyMol (Schrodinger) - Collab with Paul Sprengeler and Ken Schwinn // Relavant Files: // 1. Plugin: CorpView.py (in Mobius config dir) // 2. Script: CorpView.pml - Runs the plugin and then passes in the .csv file (in Mobius config dir) // Expected content (note: <csv-file-name> gets replaced): // import pymol // pluginLocation = 'C:\Progra~1\PyMOL\PyMOL\modules\pmg_tk\startup\CorpView.py' // cmd.do('run ' + pluginLocation) // cmd.do('lv_create_environment') // cmd.do('lv_import_mobius <csv-file-name>') // cmd.do('lv_destroy_environment') // 3. Csv file: PyMolBatchLoad.csv - Csv file in format expected by plugin (in Mobius temp dir) if (ExportToPyMol.Checked) { ExportToPyMolMethod(url, densityMapUrl, target, qm); } else if (ExportToMOE.Checked) // Export to MOE { ExportToMOEMethod(url, qm, fmtr, rfld); } else { ExportToFilesMethod(url, qm, fmtr, rfld); } }
/// <summary> /// Setup/configure any needed secondary metabroker /// </summary> /// <param name="eqp"></param> /// <param name="mpd"></param> /// <param name="unpivotedTableIsFirst"></param> void SetupSecondaryMetaBroker( ExecuteQueryParms eqp, MultiTablePivotBrokerTypeData mpd, out bool unpivotedTableIsFirst) { QueryEngine qe2; Query q2; QueryTable qt2; QueryColumn qc2; MetaTable mt2; string firstMtName = mpd.FirstTableName; MetaTable firstMt = MetaTableCollection.GetWithException(firstMtName); unpivotedTableIsFirst = UnpivotedAssayResult.IsUnpivotedSummarizedMdbAssayTable(firstMtName); if (unpivotedTableIsFirst) // just use unpivoted table as is { SecondaryMetaBroker = this; Eqp2 = eqp; } else // all pivoted tables, create secondary query on the summarized unpivoted table { if (SecondaryMetaBroker == null) { MultiDbAssayMetaBroker mb2 = SecondaryMetaBroker = new MultiDbAssayMetaBroker(); if (UnpivotedAssayResult.IsCombinedMdbAssayTable(firstMtName)) { mt2 = MetaTableCollection.GetWithException(MultiDbAssayDataNames.CombinedTableName); } else { mt2 = MetaTableCollection.GetWithException(MultiDbAssayDataNames.BaseTableName); } q2 = new Query(); qt2 = new QueryTable(mt2); qc2 = qt2.GetQueryColumnByNameWithException(MultiDbAssayDataNames.GeneSymbol); if (mpd.TableCodeCsvList.Length != 0) // limit by codes { qc2.Criteria = qc2.CriteriaDisplay = MultiDbAssayDataNames.GeneSymbol + " in (" + mpd.TableCodeCsvList + ")"; } q2.AddQueryTable(qt2); qe2 = new QueryEngine(); qe2.Query = q2; Eqp2 = new ExecuteQueryParms(qe2, qt2); mb2.PrepareQuery(Eqp2); mb2.Sql += " where " + qc2.Criteria; } Eqp2.SearchKeySubset = eqp.SearchKeySubset; } return; }