/// <summary>
/// Retrieve target Entrez gene id from symbol
/// </summary>
/// <param name="targetSymbol"></param>
/// <returns></returns>
public static int TargetSymbolToId(
string targetSymbol)
{
string sql =
@"select entrezgene_id
from <table>
where upper(gene_symbol) = :0" ;
DbCommandMx d = new DbCommandMx();
d.PrepareParameterized(sql, DbType.String);
d.ExecuteReader(targetSymbol.ToUpper());
if (!d.Read())
{
d.Dispose();
throw new Exception("Target Symbol not found: " + targetSymbol);
}
int targetId = d.GetInt(0);
d.Dispose();
return(targetId);
}
/// <summary>
/// Select a Molecule object for a compound id
/// </summary>
/// <param name="cid"></param>
/// <param name="mt"></param>
/// <returns></returns>
public static MoleculeMx SelectMoleculeForCid(
string cid,
MetaTable mt = null)
{
MetaColumn strMc = null;
string mtName = null, keyColName, strColExpr, chimeString;
MoleculeMx cs;
if (cid == null || cid == "")
{
return(null);
}
if (RestrictedDatabaseView.KeyIsRetricted(cid))
{
return(null);
}
//if (mt != null) mtName = mt.Name; // debug
bool isUcdb = (mt != null && mt.Root.IsUserDatabaseStructureTable); // user compound database
if (isUcdb) // if root metatable is user database then normalize based on key
{
mt = mt.Root; // be sure we have root
cid = CompoundId.Normalize(cid, mt);
}
else
{
cid = CompoundId.Normalize(cid, mt);
cs = MoleculeCache.Get(cid); // see if in cache
if (cs != null)
{
return(cs);
}
mt = CompoundId.GetRootMetaTableFromCid(cid, mt);
if (mt != null && Lex.Eq(mt.Name, "corp_structure") && MetaTableCollection.Get("corp_structure2") != null)
{
mt = MetaTableCollection.Get("corp_structure2"); // hack to search both small & large mols for Corp database
}
}
if (mt == null)
{
return(null);
}
strMc = mt.FirstStructureMetaColumn; //.getmt.GetMetaColumnByName("molstructure");
if (strMc == null)
{
return(null);
}
cid = CompoundId.NormalizeForDatabase(cid, mt);
if (String.IsNullOrEmpty(cid))
{
return(null);
}
// Call external method to select structure
if (strMc.ColumnMap.StartsWith("InternalMethod", StringComparison.OrdinalIgnoreCase) ||
strMc.ColumnMap.StartsWith("PluginMethod", StringComparison.OrdinalIgnoreCase))
{ // call external method to get structure
List <MetaColumn> selectList = new List <MetaColumn>();
selectList.Add(mt.KeyMetaColumn);
selectList.Add(strMc);
object[] vo = new object[2];
vo[0] = cid;
try { GenericMetaBroker.CallLateBindMethodToFillVo(vo, 1, mt, selectList); }
catch (Exception ex)
{ return(null); }
if (vo[1] is MoleculeMx)
{
cs = (MoleculeMx)vo[1];
}
else if (vo[1] is string)
{
cs = MoleculeMx.MolStringToMoleculeMx((string)vo[1]);
}
else
{
cs = null;
}
if (!isUcdb)
{
MoleculeCache.AddMolecule(cid, cs);
}
return(cs);
}
// Normal case
//if (HelmEnabled) // temp till server back
//{
// cs = new MoleculeMx();
// MoleculeMx.SetMoleculeToTestHelmString(cid, cs);
// return cs;
//}
string tableMap = mt.GetTableMapWithAliasAppendedIfNeeded(); // some SQL (e.g. Postgres) requires an alias for subqueries)
strColExpr = strMc.ColumnMap;
if (strColExpr == "")
{
strColExpr = strMc.Name;
}
if (MqlUtil.IsCartridgeMetaTable(mt)) // selecting from Direct cartridge
{
if (!Lex.Contains(tableMap, "chime(")) // if no chime expression
{
strColExpr = "chime(ctab)"; // then create one (gets clob)
}
strColExpr += ", chime(ctab)"; // add 2nd column that gets clob in case first just gets first 4000 characters
}
keyColName = mt.KeyMetaColumn.ColumnMap;
if (keyColName == "")
{
keyColName = mt.KeyMetaColumn.Name;
}
DbType parmType = DbType.String;
object cidObj = cid;
if (mt.KeyMetaColumn.IsNumeric)
{
if (!Lex.IsInteger(cid))
{
return(null); // if numeric col be sure cid is numeric also
}
parmType = DbType.Int64;
cidObj = Int64.Parse(cid);
}
string sql =
"select " + strColExpr + " " +
"from " + tableMap + " " +
"where " + keyColName + " = :0";
DbCommandMx drd = new DbCommandMx();
try
{
drd.PrepareParameterized(sql, parmType);
drd.ExecuteReader(cidObj);
if (!drd.Read() || drd.Rdr.IsDBNull(0))
{
drd.Dispose();
return(null);
}
string molString = drd.GetString(0);
drd.Dispose();
MoleculeMx.TrySetStructureFormatPrefix(ref molString, strMc.DataTransform); // add molString type if indicated by data transform
cs = MoleculeMx.MolStringToMoleculeMx(molString);
cs.StoreKeyValueInMolComments(strMc, cid);
if (!isUcdb)
{
MoleculeCache.AddMolecule(cid, cs);
}
//if (MoleculeMx.HelmEnabled == true && Lex.IsInteger(cid))
// MoleculeMx.SetMoleculeToTestHelmString(cid, cs);
return(cs);
}
catch (Exception ex)
{ // just log message & return;
DebugLog.Message("SelectMoleculeForCid Exception, Cid: " + cid + ", table: " + mt.Name + "\n" +
"sql: " + OracleMx.FormatSql(sql) + "\n" + DebugLog.FormatExceptionMessage(ex));
if (drd != null)
{
drd.Dispose();
}
return(null);
}
}
TextReader GetAssayXml(
string aid)
{
TextReader rdr = null;
DbCommandMx dao = null;
string xml;
// Try to get from file first
if (PubChemAssayDirectory == null)
{
PubChemAssayDirectory = ServicesIniFile.Read("PubChemAssayDirectory");
}
if (PubChemAssayDirectory != "")
{
try
{
string fileName = PubChemAssayDirectory + @"\CSV\Description\" + aid + ".descr.xml";
StreamReader sr = new StreamReader(fileName);
xml = sr.ReadToEnd();
sr.Close();
if (String.IsNullOrEmpty(xml))
{
return(null);
}
return(new StringReader(xml));
}
catch (Exception ex) { }
}
// Try to get from table (new method)
string sql =
"select xml " +
"from mbs_owner.mbs_pbchm_assy_xml " +
"where aid = :0";
try
{
dao = new DbCommandMx();
dao.PrepareParameterized(sql, DbType.String);
dao.ExecuteReader(aid);
if (!dao.Read())
{
throw new Exception("Not found");
}
xml = dao.GetString(0);
dao.Dispose();
}
catch (Exception ex)
{
dao.Dispose();
return(null);
}
if (String.IsNullOrEmpty(xml))
{
return(null);
}
return(new StringReader(xml));
}
/// <summary>
/// Get Assay data including biological target(s) & gene(s) for a single Assay or all Assays
/// </summary>
/// <param name="assayId">Assay to get data for or -1 to get for all assays</param>
/// <returns></returns>
public static Dictionary <int, AssayDbMetadata> GetAssayDbDict
(int assayId)
{
if (ForceAssayId)
{
assayId = ForcedAssayId;
}
else if (AssayTargetGeneDataDict != null)
{
return(AssayTargetGeneDataDict);
}
string sql = @"
SELECT <columns>
FROM <tables>
WHERE 1 = 1
and <conditions>" ;
DbCommandMx drd = new DbCommandMx();
if (assayId > 0) // get for single assay
{
sql = sql.Replace("1=1", "<keyColExpr> = :0");
drd.PrepareParameterized(sql, DbType.Int32);
drd.ExecuteReader(assayId);
}
else // get for all assays
{
drd.Prepare(sql);
drd.ExecuteReader();
}
Dictionary <int, AssayDbMetadata> dict = new Dictionary <int, AssayDbMetadata>();
if (ForceAssayId)
{
AssayTargetGeneDataDict = dict;
}
AssayDbMetadata assay = null;
AssayDbTarget target = null;
int currentAssayId = -1;
int currentTrgtId = -1;
while (true)
{
if (!drd.Read())
{
break;
}
assayId = drd.GetInt(0);
string assayName = drd.GetString(1);
string MthdVrsnMdTxt = drd.GetString(2);
if (currentAssayId < 0 || currentAssayId != assayId)
{
assay = new AssayDbMetadata();
assay.AssayId = assayId;
assay.Name = assayName;
if (Lex.Eq(MthdVrsnMdTxt, "Single Point")) // translate values
{
assay.SP = true;
}
else if (Lex.Eq(MthdVrsnMdTxt, "Conc./Dose Response Curve"))
{
assay.CRC = true;
}
dict[assayId] = assay;
currentAssayId = assayId;
currentTrgtId = -1;
}
int trgtId = drd.GetInt(3); // target id
if (currentTrgtId < 0 || currentTrgtId != trgtId)
{ // new target
target = new AssayDbTarget();
target.TargetId = trgtId;
target.TargetName = drd.GetString(4); // target name
target.TargetDesc = drd.GetString(5); // target desc
target.TargetTypeName = drd.GetString(6); // e.g. G protein coupled receptor
target.TargetTypeShortName = drd.GetString(7); // e.g. GPCR
assay.Targets.Add(target);
currentTrgtId = trgtId;
}
string geneId = drd.GetString(8);
if (String.IsNullOrEmpty(geneId))
{
continue;
}
AssayDbGene gene = new AssayDbGene();
gene.GeneId = geneId;
gene.GeneSymbol = drd.GetString(9);
target.Genes.Add(gene);
}
drd.Dispose();
return(dict);
}
/// <summary>
/// Return description for table.
/// May be html or simple text
/// </summary>
/// <param name="mt"></param>
/// <returns></returns>
public static TableDescription GetTableDescription(
MetaTable mt)
{
DateTime dt;
bool firstRow;
double d1;
int assayId, i1;
string geneSymbol, geneId, geneLinkUrl = "", html = "", url, txt;
TableDescription td = new TableDescription();
try
{
string templateFile = CommonConfigInfo.MiscConfigDir + @"<templateName>";
StreamReader sr = new StreamReader(templateFile);
html = sr.ReadToEnd();
sr.Close();
}
catch (Exception ex)
{
td.TextDescription = "Error: " + ex.Message;
return(td);
}
try
{ geneLinkUrl = ServicesIniFile.Read("LsgEntrezGeneUrlTemplate"); }
catch (Exception ex) { }
try { assayId = Convert.ToInt32(mt.Code); }
catch (Exception ex) { return(null); }
AssayDbMetadata assay = AssayMetadataDao.GetAssayTargetGeneData(assayId); // get biological target(s) & gene(s) for assay
string btStr = "";
string gsStr = "";
List <AssayDbTarget> targets = assay.Targets;
foreach (AssayDbTarget target in assay.Targets)
{
string bt = target.TargetName;
if (bt == "")
{
continue;
}
bt += "<Target_Link>";
if (target.TargetDesc != "")
{
bt += " - " + target.TargetDesc;
}
if (btStr.IndexOf(bt) < 0) // new bio target
{
if (gsStr != "")
{
gsStr = " (" + gsStr + ")";
}
btStr = btStr.Replace("<Target_Link>", gsStr);
gsStr = "";
btStr += "<br> " +
bt;
}
if (target.Genes != null && target.Genes.Count > 0)
{ // add link to gene if appropriate
AssayDbGene gene = target.Genes[0]; // just first gene for now
geneSymbol = gene.GeneSymbol;
geneId = gene.GeneId;
geneLinkUrl =
"http:////Mobius/command?ShowContextMenu:TargetContextMenu(<Gene_Link>)";
if (!String.IsNullOrEmpty(geneLinkUrl))
{ // include link to gene description
if (Lex.IsDefined(geneId))
{
url = geneLinkUrl.Replace("<Gene_Link>", geneId); // link on gene id
}
else
{
url = geneLinkUrl.Replace("<Gene_Link>", gene.GeneSymbol); // link on symbol
}
geneSymbol = // define tag to open in new window
"<a href=\"" + url + "\">" + geneSymbol + "</a>";
}
if (gsStr != "")
{
gsStr += ", "; // same bt additional gene
}
gsStr += geneSymbol;
}
}
if (gsStr != "")
{
gsStr = " (" + gsStr + ")";
}
btStr = btStr.Replace("<Target_Link>", gsStr); // plug in last gene symbol
html = html.Replace("blgcl_trgt_text", btStr);
// Method type & descriptors
string methodDescrSql = @"
SELECT <columns>
FROM <tables>
WHERE <criteria>
ORDER BY LOWER(<orderCols>)";
DbCommandMx drd = new DbCommandMx();
drd.PrepareParameterized(methodDescrSql, DbType.Int32);
assayId = Convert.ToInt32(mt.Code);
drd.ExecuteReader(assayId);
string mdStr = "";
firstRow = true;
while (true)
{
if (!drd.Read())
{
break;
}
if (firstRow) // get single assay parameters
{
firstRow = false;
}
string mdtn = "<methodTypeName>";
if (mdtn == "")
{
continue;
}
string bmdn = drd.GetStringByName("<methodDescriptionColumn>");
string bmddt = drd.GetStringByName("<methodDescriptionColumn2>");
mdtn = "<br> " +
mdtn + ": " + bmdn;
if (bmddt != "" && bmdn.ToLower().IndexOf(bmddt.ToLower()) < 0) // include any description if don't already have
{
mdtn += " - " + bmddt;
}
mdStr += mdtn;
}
html = html.Replace("<methodDescriptionPlaceHolder>", mdStr);
// Get Minimum Significant Results information
string msrSql = @"
SELECT <columns>
FROM <tables>
WHERE <conditions>" ;
// RDM MSR Sql (obsolete)
string msrSqlRdm = @"<todo>";
drd.PrepareParameterized(msrSql, DbType.Int32);
assayId = Convert.ToInt32(mt.Code);
drd.ExecuteReader(assayId);
while (true)
{
if (!drd.Read())
{
break; // should always get at least one row even if no MSR data
}
}
// Pharmacological action
// <todo>
// Research effort & therapeutic target
// <todo>
// Biological Conditions
// <todo>
// All done
drd.Dispose();
td.TextDescription = html;
return(td);
}