/// <summary>
/// Select
/// </summary>
/// <param name="projId"></param>
/// <returns></returns>
public static List <AfsTarget> Select(int projId)
{
List <AfsTarget> l = new List <AfsTarget>();
string sql = @"
select *
from <mbs_owner>.afs_target
where afs_current = 1 and proj_id = " + projId +
" order by upper(target_name)";
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
AfsTarget t = new AfsTarget();
t.ProjId = dao.GetIntByName("PROJ_ID");
t.TargetName = dao.GetStringByName("TARGET_NAME");
t.TargetType = dao.GetStringByName("TARGET_TYPE");
l.Add(t);
}
dao.CloseReader();
return(l);
}
/// <summary>
/// Select
/// </summary>
/// <param name="projId"></param>
/// <returns></returns>
public static List <AfsProjMeta> Select(int projId)
{
List <AfsProjMeta> l = new List <AfsProjMeta>();
string sql = @"
select *
from <mbs_owner>.afs_proj_meta
where afs_current = 1 and proj_id = " + projId +
" order by upper(category_name)";;
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
AfsProjMeta m = new AfsProjMeta();
m.ProjId = dao.GetIntByName("PROJ_ID");
m.CategoryName = dao.GetStringByName("CATEGORY_NAME");
m.CategoryValue = dao.GetStringByName("CATEGORY_VALUE");
l.Add(m);
}
dao.CloseReader();
return(l);
}
/// <summary>
/// GetProjectMetaDataWithCriteria
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public static AfsProject SelectMetaDataWithCriteria(string criteria)
{
string sql = @"
select *
from <mbs_owner>.afs_project
where afs_current = 1 and <criteria>" ;
sql = Lex.Replace(sql, "<mbs_owner>", AfsTableSchema);
sql = Lex.Replace(sql, "<criteria>", criteria);
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
if (!dao.Read())
{
dao.CloseReader();
return(null);
}
AfsProject p = new AfsProject();
p.ProjId = dao.GetIntByName("PROJ_ID");
p.ProjectLabel = dao.GetStringByName("PROJ_NAME");
p.PlatformName = dao.GetStringByName("PLATFORM_NAME");
p.Description = dao.GetStringByName("DESCRIPTION");
p.ProjectFlowScheme = dao.GetClobByName("PROJ_FLOW_SCHEME");
p.MbsDhtFolderName = dao.GetStringByName("MBS_DHT_FOLDER_CODE");
p.MbsProjectName = dao.GetStringByName("MBS_PROJECT_CODE");
dao.CloseReader();
p.ProjMeta = AfsProjMeta.Select(p.ProjId);
p.Targets = AfsTarget.Select(p.ProjId);
p.Assays = AfsAssay.Select(p.ProjId);
p.Libraries = AfsLibrary.Select(p.ProjId);
return(p);
}
public static List <DbColumnMetadata> GetTableMetadataFromMySqlDictionary(
DbConnectionMx conn,
string schema,
string tableName)
{
string sql = String.Format(
@"SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
table_schema = '{0}'
AND table_name = '{1}'
ORDER BY ORDINAL_POSITION" ,
schema, tableName);
DbCommandMx drd = new DbCommandMx();
drd.MxConn = conn;
drd.PrepareUsingDefinedConnection(sql);
DbDataReader rdr = drd.ExecuteReader();
List <DbColumnMetadata> md = new List <DbColumnMetadata>();
while (rdr.Read())
{
DbColumnMetadata cmd = new DbColumnMetadata();
cmd.Name = drd.GetStringByName("column_name");
cmd.Type = drd.GetStringByName("data_type");
cmd.Length = drd.GetLongByName("character_maximum_length");
cmd.Precision = drd.GetIntByName("numeric_precision");
cmd.Scale = drd.GetIntByName("numeric_scale");
cmd.Nullable = drd.GetStringByName("is_nullable");
cmd.Comment = drd.GetStringByName("column_comment");
md.Add(cmd);
}
rdr.Close();
return(md);
}
/// <summary>
/// Get dict relating types between different assay databases
/// </summary>
static void BuildResultTypeDicts()
{
string sql = @"
select <columns>
from <tables>
order by lower(<orderColumns>)";
resultTypeDict1 = new Dictionary <int, AssayResultType>();
resultTypeDict2 = new Dictionary <int, AssayResultType>();
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
while (drd.Read())
{
AssayResultType r = new AssayResultType();
r.Name = drd.GetStringByName("rslt_typ_nm");
r.ResultTypeId1 = drd.GetIntByName("rslt_typ_id");
r.ResultTypeId2 = drd.GetIntByName("SECONDARY_RSLT_TYP_ID");
r.SummarizedMeanTypeCode = drd.GetStringByName("smrzd_mean_typ_cd");
r.DimensionTypeId = drd.GetIntByName("dmnsn_typ_id");
if (r.DimensionTypeId != 70) // dmnsn_typ_id: 70 = 'Result', 61 = 'Stat'
{
r.ResultTypeId2 = -r.ResultTypeId2;
}
resultTypeDict1[r.ResultTypeId1] = r;
resultTypeDict2[r.ResultTypeId2] = r;
}
drd.Dispose();
return;
}
/// <summary>
/// GetTableMetadataFromOracleDictionary
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static List <DbColumnMetadata> GetTableMetadataFromOracleDictionary(
DbConnectionMx conn,
string schemaName,
string tableName)
{
int t0 = TimeOfDay.Milliseconds();
DbColumnMetadata cmd;
List <DbColumnMetadata> cmdList = new List <DbColumnMetadata>();
string sql = "select column_name,data_type,data_length,data_precision,data_scale,nullable " +
"from sys.all_tab_columns where owner=:0 " +
"and table_name=:1 order by column_id";
if (conn == null)
{
throw new Exception("Connection not found for tableName: " + tableName);
}
DbCommandMx drd = new DbCommandMx();
drd.MxConn = conn;
int parmCount = 2;
drd.PrepareMultipleParameter(sql, parmCount);
string[] sa = tableName.Split('.');
if (sa.Length != 2)
{
throw new Exception("TableName not in owner.tableName form: " + tableName);
}
string creator = sa[0];
string tname = sa[1];
if (Lex.Eq(creator, "mbs_user")) // workaround to allow tables owned by dev mbs_owner
{
creator = "mbs_owner"; // to be accessed via synonyms defined on dev mbs_user
}
object[] p = new object[2];
p[0] = creator.ToUpper();
p[1] = tname.ToUpper();
drd.ExecuteReader(p);
while (drd.Read())
{
cmd = new DbColumnMetadata();
cmd.Name = drd.GetStringByName("column_name");
cmd.Type = drd.GetStringByName("data_type");
cmd.Length = drd.GetIntByName("data_length");
cmd.Precision = drd.GetIntByName("data_precision");
cmd.Scale = drd.GetIntByName("data_scale");
cmd.Nullable = drd.GetStringByName("nullable");
cmdList.Add(cmd);
}
drd.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
return(cmdList);
}
/// <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);
}