/// <summary> /// Convert a multipivot table into a set of tables where data exists for /// one or more of the compound identifiers in the list. /// </summary> /// <param name="qt">Current form of query table</param> /// <param name="q">Query to add transformed tables to</param> /// <param name="ResultKeys">Keys data will be retrieved for</param> public override void ExpandToMultipleTables( QueryTable qt, Query q, List <string> resultKeys) { MetaTable mt2; QueryTable qt2; string sql; int methodId, i1; int t0 = TimeOfDay.Milliseconds(); List <string> normalizedResultKeys = new List <string>(); for (i1 = 0; i1 < resultKeys.Count; i1++) // copy keys to parameter array properly normalized { string key = CompoundId.NormalizeForDatabase(resultKeys[i1], qt.MetaTable); if (key == null) { key = NullValue.NullNumber.ToString(); // if fails supply a "null" numeric value } normalizedResultKeys.Add(key); } sql = // todo: Make to work in general case (PubChem only now) "select mthd_vrsn_id " + "from " + "mbs_owner.mbs_pbchm_rslt" + " " + "where ext_cmpnd_id_nbr in (<list>) " + " and sts_id = 1 " + // active records only "group by mthd_vrsn_id"; DbCommandMx drd = new DbCommandMx(); drd.PrepareListReader(sql, DbType.Int32); drd.ExecuteListReader(normalizedResultKeys); if (drd.Cancelled) { // todo qe.Cancelled = true; drd.Dispose(); return; } Hashtable mtHash = new Hashtable(); int methodIdCount = 0; while (true) // convert list of methods to set of metatable names { if (!drd.ListRead()) { break; } methodId = drd.GetInt(0); string mtName = "pubchem_aid_" + methodId.ToString(); // todo: Make to work in general case (PubChem only now) if (QueryEngine.FilterAllDataQueriesByDatabaseContents && !MetaTableCollection.IsMetaTableInContents(mtName)) { continue; // metatable must be in contents } if (qt.MetaTable.UseSummarizedData) { mtName += MetaTable.SummarySuffix; } mtHash[mtName] = null; methodIdCount++; } drd.Dispose(); if (drd.Cancelled) { // todo qe.Cancelled = true; return; } ArrayList mtList = new ArrayList(); foreach (string mtName2 in mtHash.Keys) { // put metatable labels & names into a list for sorting mt2 = MetaTableCollection.Get(mtName2); if (mt2 == null) { continue; } if (mt2.Parent == null) { continue; // skip if no parent } mtList.Add(mt2.Label.ToLower().PadRight(64) + "\t" + mt2.Name); } mtList.Sort(); foreach (string mts in mtList) { // add new querytables/metatables to query string[] sa = mts.Split('\t'); mt2 = MetaTableCollection.Get(sa[1]); if (mt2 == null) { continue; } qt2 = new QueryTable(q, mt2); if (qt.HeaderBackgroundColor != Color.Empty) { qt2.HeaderBackgroundColor = qt.HeaderBackgroundColor; } } t0 = TimeOfDay.Milliseconds() - t0; return; }