/// <summary>
/// (OLD VERSION)
/// Get list of compounds whose fragments match those of the compounds in the list.
/// </summary>
/// <param name="cnList"></param>
/// <returns></returns>
public static Dictionary <string, List <string> > GetAllSaltFormsNew(
List <string> cnList)
{
int t0, t1;
t0 = TimeOfDay.Milliseconds();
Dictionary <string, List <string> > cidDict = new Dictionary <string, List <string> >();
List <string> cnList2 = new List <string>();
foreach (string s in cnList)
{ // get just the list entries that are integers (e.g. remove MFCD numbers)
if (Lex.IsInteger(s))
{
cnList2.Add(s);
}
}
t1 = TimeOfDay.Milliseconds() - t0;
if (cnList2.Count == 0)
{
return(cidDict);
}
//MetaTable mt = MetaTableCollection.Get("frag_occurrence");
MetaTable mt = MetaTableCollection.Get("CorpId_salt_isomer_info");
if (mt == null)
{
return(cidDict);
}
string sql = mt.TableMap; // get sql to use from metatable
if (sql.StartsWith("("))
{
sql = sql.Substring(1, sql.Length - 2); // remove surround parens if necessary
}
sql = Lex.Replace(sql, "where", "where CorpId in (<list>) and "); // add criteria needed to do list search
DbCommandMx drd = new DbCommandMx();
try
{
drd.PrepareListReader(sql, DbType.Int32);
drd.ExecuteListReader(cnList2);
if (drd.Cancelled)
{
drd.Dispose();
return(null);
}
while (true)
{
if (!drd.ListRead())
{
break;
}
string cn = CompoundId.Normalize(drd.GetInt(0).ToString());
string cn2 = CompoundId.Normalize(drd.GetInt(1).ToString());
if (!cidDict.ContainsKey(cn))
{
cidDict[cn] = new List <string>();
}
List <string> al = cidDict[cn];
if (al.Count == 0 || al[al.Count - 1] != cn2) // add if not dup
{
al.Add(cn2);
}
}
drd.Dispose();
}
catch (Exception ex)
{ // catch case non-numeric item in list, single-row subquery returns more than one row, etc.
drd.Dispose();
return(new Dictionary <string, List <string> >());
}
t1 = TimeOfDay.Milliseconds() - t0;
return(cidDict);
}
/// <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;
}