/// <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);
}
