/// <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>
/// Get image coordinates
/// </summary>
/// <param name="targetMapXDict"></param>
/// <param name="targetMapYDict"></param>
public static void GetImageCoords(
out Dictionary <int, double> targetMapXDict,
out Dictionary <int, double> targetMapYDict)
{
targetMapXDict = new Dictionary <int, double>();
targetMapYDict = new Dictionary <int, double>();
string sql = @"
select entrezgene_id, x, y
from mdbassay_owner.image_coord" ;
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
while (drd.Read())
{
int geneId = drd.GetInt(0);
double x = drd.GetDouble(1);
targetMapXDict[geneId] = x;
double y = drd.GetDouble(2);
targetMapYDict[geneId] = y;
}
drd.CloseReader();
drd.Dispose();
return;
}
/// <summary>
/// Get list of ACL roles
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
public static string[] GetACLRoles(string username)
{
string[] roles = null;
try
{
DbCommandMx drd = new DbCommandMx();
drd.PrepareMultipleParameter("SELECT acl_api_pkg.acl_get_user_roles_fnc(:0) FROM DUAL", 1);
DbDataReader odr = drd.ExecuteReader(username.ToUpper());
string response = null;
if (odr.Read())
{
response = odr.GetString(0);
}
drd.CloseReader();
drd.Dispose();
if (response != null && response.StartsWith("OK") && response.IndexOf("|") > 0)
{
roles = response.Substring(response.IndexOf("|") + 1).Split(new char[] { ' ', ';' });
}
}
catch (Exception ex)
{
//do nothing
}
return(roles);
}
/// <summary>
/// Select basic project info with criteria
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public static List <AfsProject> SelectWithCriteria(string criteria)
{
MetaTreeNode mtn;
string sql = @"
select
proj_id,
mbs_project_code,
proj_name,
mbs_dht_folder_code,
dht_folder_name,
platform_name
from
<mbs_owner>.afs_project p
where
afs_current = 1
and mbs_project_code is not null
and <criteria>
order by upper(dht_folder_name), upper(proj_name)
" ;
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
sql = Lex.Replace(sql, "<criteria>", criteria);
List <AfsProject> projects = new List <AfsProject>();
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
AfsProject p = new AfsProject();
p.ProjId = dao.GetInt(0);
p.MbsProjectName = dao.GetString(1).ToUpper();
p.ProjectLabel = dao.GetString(2);
if (Lex.IsNullOrEmpty(p.ProjectLabel))
{
p.ProjectLabel = p.MbsProjectName;
}
p.MbsDhtFolderName = dao.GetString(3).ToUpper();
p.DhtFolderLabel = dao.GetString(4).ToUpper();
if (Lex.IsNullOrEmpty(p.DhtFolderLabel))
{
p.DhtFolderLabel = p.MbsDhtFolderName;
}
p.PlatformName = dao.GetString(5).ToUpper();
projects.Add(p);
}
dao.CloseReader();
return(projects);
}
/// <summary>
///
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public static List <AfsAssay> SelectWithCriteria(string criteria)
{
MetaTreeNode mtn;
string sql = @"
select
p.proj_id,
p.mbs_project_code,
proj_name,
p.mbs_dht_folder_code,
dht_folder_name,
platform_name,
assay_id,
assay_name,
assay_db,
assay_use
from
<mbs_owner>.afs_project p,
<mbs_owner>.afs_assay a
where
p.afs_current = 1
and p.mbs_project_code is not null
and a.afs_current = 1
and a.proj_id = p.proj_id
and <criteria>
order by upper(dht_folder_name), upper(proj_name), upper(assay_name)
" ;
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
sql = Lex.Replace(sql, "<criteria>", criteria);
List <AfsAssay> assays = new List <AfsAssay>();
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
AfsAssay a = new AfsAssay();
a.ProjId = dao.GetInt(0);
a.MbsProjectName = dao.GetString(1).ToUpper();
a.ProjectLabel = dao.GetString(2);
a.MbsDhtFolderName = dao.GetString(3).ToUpper();
a.DhtFolderLabel = dao.GetString(4).ToUpper();
a.Platform = dao.GetString(5).ToUpper();
a.AssayId = dao.GetInt(6);
a.AssayLabel = dao.GetString(7);
a.AssayDb = dao.GetString(8).ToUpper();
a.AssayUse = dao.GetString(9).ToUpper();
assays.Add(a);
}
dao.CloseReader();
return(assays);
}
/// <summary>
/// Calculate & persist library stats
/// </summary>
/// <returns></returns>
public static string UpdateLibraryStatistics()
{
Dictionary <string, MetaTableStats> stats = new Dictionary <string, MetaTableStats>();
int libId;
long cnt;
DateTime dt;
string libName, txt;
try
{
DbCommandMx dao = new DbCommandMx();
string sql = // get count and date (must use crt_timestamp) for each library
@"
select
l.lib_id,
count(*),
l.crt_timestamp
from
corp_owner.corp_library l,
corp_owner.corp_library_substance ls
where ls.lib_id (+) = l.lib_id
group by l.lib_id, l.crt_timestamp
order by l.lib_id" ;
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
libId = dao.GetInt(0);
libName = "LIBRARY_" + libId;
if (!stats.ContainsKey(libName))
{
stats[libName] = new MetaTableStats();
}
cnt = dao.GetLong(1);
stats[libName].RowCount = cnt;
dt = dao.GetDateTime(2);
stats[libName].UpdateDateTime = dt;
}
dao.CloseReader();
dao.Dispose();
string fileName = MetaTableFactory.MetaTableXmlFolder + @"\LibraryStats";
MetaTableFactory.WriteMetaTableStats(stats, fileName);
}
catch (Exception ex)
{
throw ex;
}
return("Updated statistics for " + stats.Count + " libraries");
}
/// <summary>
/// Read dictionary from Oracle table
/// </summary>
/// <param name="dict"></param>
/// <returns></returns>
static DictionaryMx ReadDictionaryFromOracle(
DictionaryMx dict)
{
DbCommandMx drd = null;
int t0, t1, i1;
try
{
//if (Lex.Eq(dict.Name, "DIT_PERSON")) dict = dict; // debug
DbDataReader dr = null;
drd = new DbCommandMx();
drd.Prepare(dict.Sql);
dr = drd.ExecuteReader();
t0 = TimeOfDay.Milliseconds();
while (drd.Read())
{
if (!dr.IsDBNull(0))
{
string word = dr.GetValue(0).ToString();
if (Lex.IsNullOrEmpty(word))
{
continue;
}
string definition = null;
if (!dr.IsDBNull(1))
{
definition = dr.GetValue(1).ToString();
}
dict.Add(word, definition);
t1 = TimeOfDay.Milliseconds();
// if (t1-t0 > 2000) break; // limit time for development
}
}
drd.CloseReader();
drd.Dispose();
t1 = TimeOfDay.Milliseconds() - t0;
// DebugLog.Message("ReadDictionaryFromOracle " + dict.Name + " Time: " + t1.ToString());
return(dict);
}
catch (Exception ex)
{
if (drd != null)
{
drd.Dispose();
}
return(null);
}
}
/// <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);
}
/// <summary>
/// Get list of CIDS that are in the oracle database but not the Mobius fingerprint files
/// </summary>
/// <returns></returns>
static List <string> GetMissingCidList()
{
string sql = "", cid;
int readCnt = 0;
HashSet <string> knownCidsSet = FpDao.GetExistingCidSet(); // get list of cids in DB
ExistingUndefinedStructureCids = FpDao.ReadUndefinedStructuresCids(); // cids that have undefined structures and aren't in DB
knownCidsSet.UnionWith(ExistingUndefinedStructureCids);
List <string> cidList = new List <string>();
if (CorpDatabase)
{
sql = SelectAllCorpIds;
//sql = Lex.Replace(sql, "s.corp_nbr = m.corp_nbr", "s.corp_nbr = m.corp_nbr and s.corp_nbr = 3431641"); // debug
//sql = Lex.Replace(sql, "s.corp_nbr = m.corp_nbr", "s.corp_nbr = m.corp_nbr and s.corp_nbr between 1000000 and 1100000"); // debug
}
else // chembl
{
sql = SelectAllChemblIds;
}
DbCommandMx rdr = DbCommandMx.PrepareAndExecuteReader(sql);
while (rdr.Read())
{
readCnt++;
if (CorpDatabase)
{
int corpId = rdr.GetInt(0); // corp_nbr
cid = corpId.ToString();
cid = CompoundId.NormalizeForDatabase(cid);
}
else
{
cid = rdr.GetString(0); // chembl
}
if (!knownCidsSet.Contains(cid))
{
cidList.Add(cid);
}
}
rdr.CloseReader();
return(cidList);
}
/// <summary>
/// Get dictionary of result types
/// </summary>
/// <returns></returns>
public static Dictionary <int, AssayDbResultType> GetResultTypeDict()
{
if (ResultTypeDict != null)
{
return(ResultTypeDict);
}
string sql = @"
select
rt.assay_rslt_typ_id,
rt.blgcl_rslt_typ_shrt_nm,
rt.sum_md_txt,
l.rslt_lvl_nm
from
metadata_owner.rslt_typ rt,
metadata_owner.rslt_lvl l
where
l.rslt_lvl_id = rt.rslt_lvl_id
and rt.sts_id = 1
and l.sts_id = 1";
Dictionary <int, AssayDbResultType> dict = new Dictionary <int, AssayDbResultType>();
ResultTypeDict = dict;
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
while (true)
{
if (!drd.Read())
{
break;
}
AssayDbResultType rt = new AssayDbResultType();
rt.RsltTypeId = drd.GetInt(0);
rt.Name = drd.GetString(1);
rt.SumMdTxt = drd.GetString(2);
rt.RsltLvl = drd.GetString(3);
dict[rt.RsltTypeId] = rt;
}
drd.CloseReader();
return(dict);
}
/// <summary>
/// Get the AFS project id, if any, associated with a project node name
/// </summary>
/// <param name="projNodeName"></param>
/// <returns></returns>
public static int GetAfsProjectId(string projNodeName)
{
string sql = @"
select proj_id
from <mbs_owner>.afs_project
where afs_current = 1 and MBS_PROJECT_CODE = '" + projNodeName.ToUpper() + "'";
sql = Lex.Replace(sql, "<mbs_owner>", AfsTableSchema);
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
if (!dao.Read())
{
dao.CloseReader();
return(-1);
}
int projId = dao.GetInt(0);
dao.CloseReader();
return(projId);
}
/// <summary>
/// Get the next value for the sequence (Oracle)
/// </summary>
/// <param name="seqName"></param>
/// <returns></returns>
public static long NextValLongOracle(
string seqName)
{
string sql;
long nextVal;
SequenceDao seqDao = Lookup(seqName);
Queue <long> seqQueue = seqDao.Queue;
if (seqQueue.Count > 0)
{
nextVal = seqQueue.Dequeue();
return(nextVal);
}
if (seqDao.CacheSize <= 0)
{
sql = "select " + seqName + ".nextval from dual";
}
else
{
sql = "select /*+ first_rows */ " + seqName + ".nextval from sys.all_catalog where rownum <= " +
(seqDao.CacheSize + 1).ToString();
}
int t0 = TimeOfDay.Milliseconds();
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
if (!drd.Read())
{
throw (new Exception("SequenceDao.NextVal Read failed"));
}
nextVal = drd.GetLong(0); // return this one
while (drd.Read()) // enqueue the rest
{
seqQueue.Enqueue(drd.GetLong(0));
}
drd.CloseReader();
drd.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
// DebugLog.Message("Read sequence, set size = " + seqQueue.Count.ToString() + ", Time(ms) = " + t0.ToString());
return(nextVal);
}
/// <summary>
/// Read the list of compounds for a library
/// </summary>
/// <param name="libId"></param>
/// <returns></returns>
public static CidList ReadLibrary(
int libId)
{
CidList l = new CidList();
DbCommandMx dao = new DbCommandMx();
string sql = @"
SELECT l.library_name, l.library_desc_text, s.corp_nbr
FROM corp_owner.corp_substance s,
corp_owner.corp_library_substance ls,
corp_owner.corp_library l
WHERE
l.lib_id = <libId> and
s.cpd_id = ls.cpd_id and
l.lib_id = ls.lib_id" ;
sql = Lex.Replace(sql, "<libId>", libId.ToString());
dao.Prepare(sql);
dao.ExecuteReader();
MetaTable rootMt = MetaTableCollection.Get(MetaTable.PrimaryRootTable);
while (dao.Read())
{
if (Lex.IsNullOrEmpty(l.UserObject.Name))
{
string name = dao.GetString(0);
if (Lex.IsNullOrEmpty(name) || Lex.IsInteger(name))
{
name = dao.GetString(1); // use desc if no name or just a number
}
if (Lex.IsNullOrEmpty(name))
{
name = "Library " + libId;
}
l.UserObject.Name = name;
}
int intCorpId = dao.GetInt(2);
string corpId = CompoundId.Normalize(intCorpId, rootMt);
l.Add(corpId);
}
dao.CloseReader();
dao.Dispose();
return(l);
}
public static long SelectLong(
string sql,
OracleDbType[] parmTypes = null,
object[] parmValues = null)
{
DbCommandMx cmd = DbCommandMx.PrepareExecuteAndRead(sql, parmTypes, parmValues);
if (cmd == null)
{
return(NullValue.NullNumber);
}
long value = cmd.GetLong(0);
cmd.CloseReader();
return(value);
}
public static int SelectInt(
string sql,
OracleDbType parmType,
object parmValue)
{
DbCommandMx cmd = DbCommandMx.PrepareExecuteAndRead(
sql,
new OracleDbType[] { parmType },
new object[] { parmValue });
if (cmd == null)
{
return(NullValue.NullNumber);
}
int value = cmd.GetInt(0);
cmd.CloseReader();
return(value);
}
/// <summary>
/// ReadSqlStatement
/// </summary>
/// <param name="name"></param>
/// <param name="id"></param>
/// <param name="sqlStmt"></param>
/// <param name="owner"></param>
/// <returns></returns>
public static bool ReadSqlStatement(
string name,
int version,
out string sqlStmt,
out string keyColName,
out string owner)
{
string sql = @"
select
sql,
key_col_name,
ownr_id
from dev_mbs_owner.mbs_spotfire_sql
where name = '" + name + "' and " +
"version = " + version;
DbCommandMx cmd = new DbCommandMx();
cmd.Prepare(sql);
cmd.ExecuteReader();
sqlStmt = keyColName = owner = null;
bool exists = cmd.Read();
if (exists)
{
sqlStmt = cmd.GetClob(0);
keyColName = cmd.GetString(1);
owner = cmd.GetString(2);
}
cmd.CloseReader();
cmd.Dispose();
return(exists);
}
/// <summary>
/// Get a list of CorpIds that have been added or modified since the checkpoint date
/// </summary>
/// <returns></returns>
static List <string> GetNewAndModifiedCorpIdList(
out Dictionary <string, DateTime> cidUpdateDateDict)
{
int readCnt = 0;
List <string> cidList = new List <string>();
cidUpdateDateDict = new Dictionary <string, DateTime>();
CheckpointDateTime = FpDao.ReadFingerPrintSimMxDataUpdateCheckpointDate(); // , "20-mar-2016 000000");
if (Lex.IsUndefined(CheckpointDateTime))
{
throw new Exception("Mobius UpdateSimMxCorpIdDataCheckpointDate is not defined");
}
//CheckpointDateTime = "20-mar-2016 000000"; // debug
string sql = Lex.Replace(SelectCorpIdsByDateRange, "1-jan-1900 000000", CheckpointDateTime);
DbCommandMx rdr = DbCommandMx.PrepareAndExecuteReader(sql);
while (rdr.Read())
{
readCnt++;
int corpId = rdr.GetInt(0); // corp_nbr
string cid = corpId.ToString();
cid = CompoundId.NormalizeForDatabase(cid);
cidList.Add(cid);
DateTime dt = rdr.GetDateTime(1);
cidUpdateDateDict[cid] = dt;
}
rdr.CloseReader();
return(cidList);
}
/// <summary>
/// Close reader
/// </summary>
public void Close()
{
DbCmd.CloseReader();
DbCmd.Dispose();
}
/// <summary>
/// Calculate & persist metatable stats for tables belonging to broker
/// </summary>
/// <returns></returns>
public int UpdateMetaTableStatistics()
{
MetaTableStats mts;
DateTime dt;
string sql;
int mthdId;
long rowCount;
Dictionary <int, MetaTableStats> stats = new Dictionary <int, MetaTableStats>();
DbCommandMx dao = new DbCommandMx();
// Get row count for each table
sql =
"select " +
" mthd_vrsn_id, count(*) " +
"from ( " +
" select " +
" mthd_vrsn_id, rslt_grp_id" +
" from mbs_owner.mbs_adw_rslt " +
" where " +
" sts_id = 1 " +
// " and mthd_vrsn_id = 148411 " + // debug
" group by mthd_vrsn_id, rslt_grp_id) " +
"group by mthd_vrsn_id";
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
mthdId = dao.GetInt(0);
rowCount = dao.GetLong(1);
if (!stats.ContainsKey(mthdId))
{
stats[mthdId] = new MetaTableStats();
}
stats[mthdId].RowCount = rowCount;
}
dao.CloseReader();
// Get latest update date for each table
sql =
"select " +
" mthd_vrsn_id, max(updt_dt) " +
"from mbs_owner.mbs_adw_rslt " +
"where " +
" updt_dt <= sysdate and " +
" sts_id = 1" +
// " and mthd_vrsn_id = 148411 " + // debug
"group by mthd_vrsn_id";
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
mthdId = dao.GetInt(0);
dt = dao.GetDateTime(1);
if (!stats.ContainsKey(mthdId))
{
stats[mthdId] = new MetaTableStats();
}
stats[mthdId].UpdateDateTime = dt;
}
dao.CloseReader();
dao.Dispose();
int updCnt = 0;
foreach (int mthdId_ in stats.Keys)
{
mts = stats[mthdId_];
try { UserObjectDao.UpdateUpdateDateAndCount(mthdId_, mts.UpdateDateTime, (int)mts.RowCount); }
catch (Exception ex) { continue; }
updCnt++;
}
return(stats.Count);
}
/// <summary>
/// Build platform / target / assay metatree
/// </summary>
public static void BuildPlatformMetaTree()
{
string assayId, assayDb;
string sql = @"
select gene_fmly, gene_symbl, assy_nm, assy_db, assy_id_txt
from mbs_owner.cmn_assy_atrbts
where gene_fmly is not null and gene_symbl is not null and assy_nm is not null
order by lower(gene_fmly), lower(gene_symbl), lower(assy_nm)" ;
sql = AssayAttributesDao.AdjustAssayAttrsTableName(sql);
try
{
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
string family = "";
string targetSymbol = "";
string assayName = "";
MetaTreeNode familyMtn = null, targetMtn = null, tsMtn = null, assayMtn = null;
while (drd.Read())
{
string nextFamily = drd.GetString(0);
if (Lex.Ne(family, nextFamily)) // get current family node
{
family = nextFamily;
string familyNodeName = "GFTA_" + family.Replace(" ", "_"); // build name to match node
familyMtn = MetaTreeFactory.GetNode(familyNodeName);
}
string nextTargetSymbol = drd.GetString(1);
// if (Lex.Eq(nextTargetSymbol, "Adr1a")) nextTargetSymbol = nextTargetSymbol; // debug
if (Lex.Ne(targetSymbol, nextTargetSymbol)) // add new target node if going to new target
{
targetSymbol = nextTargetSymbol;
targetMtn = new MetaTreeNode();
targetMtn.Type = MetaTreeNodeType.Target;
targetMtn.Name = "TGT_ASSYS_" + targetSymbol.ToUpper();
targetMtn.Label = targetSymbol;
targetMtn.Target = targetMtn.Name;
MetaTreeFactory.AddNode(targetMtn);
if (familyMtn != null)
{
familyMtn.Nodes.Add(targetMtn);
}
tsMtn = new MetaTreeNode(); // add node for summary view by gene symbol
tsMtn.Type = MetaTreeNodeType.MetaTable;
tsMtn.Name = MultiDbAssayDataNames.BasePivotTablePrefix + targetSymbol.ToUpper();
tsMtn.Label = targetSymbol + " Assay Results Summary";
tsMtn.Target = tsMtn.Name;
MetaTreeNode mtn2 = MetaTreeFactory.GetNode(tsMtn.Name); // node with this name already exist?
if (mtn2 == null)
{
MetaTreeFactory.AddNode(tsMtn); // add to tree if doesn't exist
}
else
{
tsMtn = mtn2; // use existing node otherwise since this label doesn't have priority
}
if (targetMtn != null)
{
targetMtn.Nodes.Add(tsMtn);
}
}
string nextAssayName = drd.GetString(2);
if (Lex.Ne(assayName, nextAssayName))
{
assayName = nextAssayName;
assayDb = drd.GetString(3);
assayId = drd.GetString(4);
assayMtn = new MetaTreeNode();
assayMtn.Type = MetaTreeNodeType.MetaTable;
assayMtn.Name = assayId;
assayMtn.Target = assayMtn.Name;
assayMtn.Label = assayName;
MetaTreeNode mtn2 = MetaTreeFactory.GetNode(assayMtn.Name); // node with this name already exist?
if (mtn2 == null)
{
MetaTreeFactory.AddNode(assayMtn); // add to tree if doesn't exist
}
else
{
assayMtn = mtn2; // use existing node otherwise since this label doesn't have priority
}
if (targetMtn != null)
{
targetMtn.Nodes.Add(assayMtn);
}
}
}
drd.CloseReader();
drd.Dispose();
}
catch (Exception ex)
{
DebugLog.Message("TargetAssayMetafactory.Build Error:\r\n" + DebugLog.FormatExceptionMessage(ex));
return;
}
}
public static void UpdateFingerprintFiles()
{
string msg = "";
long uci, lastUci = 0;
int baseFpWriteCnt = 0, fragFpWriteCnt = 0, nullFpCnt = 0, readCnt = 0;
int fi = 0;
string sql = @"
select
x.uci,
x.src_id,
x.src_compound_id,
x.src_compound_id_nbr,
s.fingerprint,
h.fingerprint
from
DEV_MBS_OWNER.CORP_UC_XREF x,
DEV_MBS_OWNER.CORP_UC_STRUCTURE s,
DEV_MBS_OWNER.CORP_UC_FIKHB_HIERARCHY h
where
s.uci = x.uci
and H.PARENT (+) = s.fikhb
/* and s.uci between 1 and 1000 */ /* debug */
order by s.uci" ;
ShowProgress("Executing select fingerprints query...");
DbCommandMx cmd = DbCommandMx.PrepareExecuteAndRead(sql);
if (cmd == null)
{
throw new Exception("No rows retrieved");
}
bool readOk = true;
BinaryWriter[] bw = FpDao.OpenFingerprintFilesForWriting();
fi = 0;
DateTime lastProgressUpdate = DateTime.Now;
while (true)
{
if (readCnt > 0) // read next row if not first row
{
readOk = cmd.Read();
}
if (readOk)
{
readCnt++;
}
if (DateTime.Now.Subtract(lastProgressUpdate).TotalSeconds > 1 || !readOk) // show progress
{
lastProgressUpdate = DateTime.Now;
int fpWriteCnt = baseFpWriteCnt + fragFpWriteCnt;
msg =
"Update Fingerprint Files\r\n" +
"\r\n" +
"Reads: " + readCnt + "\r\n" +
"Fingerprints written: " + fpWriteCnt + "\r\n" +
"Null FPs: " + nullFpCnt;
ShowProgress(msg);
}
if (!readOk)
{
break;
}
uci = cmd.GetLong(0);
int src = cmd.GetInt(1);
string cidString = cmd.GetString(2);
int cidInt = cmd.GetInt(3);
byte[] fp = cmd.GetBinary(4);
byte[] fp2 = cmd.GetBinary(5);
if (fp == null && fp2 == null)
{
nullFpCnt++;
continue;
}
if (uci != lastUci)
{
fi = (fi + 1) % FpDao.FingerprintFileCount;
lastUci = uci;
}
if (fp != null)
{
FpDao.WriteFingerprintRec(bw[fi], uci, src, cidString, fp);
baseFpWriteCnt++;
}
if (fp2 != null)
{
FpDao.WriteFingerprintRec(bw[fi], uci, src, cidString, fp2);
fragFpWriteCnt++;
}
} // read loop
cmd.CloseReader();
FpDao.CloseFingerprintFilesForWriting();
FpDao.BackupAndReplaceFingerprintFiles();
msg = "*** Update Complete ***\r\n\r\n" + msg;
ShowProgress(msg);
return;
}
/// <summary>
/// Get approximation of assay type (binding/functional) and mode (agonist, antagonist, potentiator)
/// </summary>
/// <param name="assayTypeDict"></param>
/// <param name="assayModeDict"></param>
public static void GetAssayTypesAndModes(
out Dictionary <int, string> assayTypeDict,
out Dictionary <int, string> assayModeDict)
{
assayTypeDict = new Dictionary <int, string>();
assayModeDict = new Dictionary <int, string>();
string sql = @"
select <columns>
from <tables>
where 1=1
and <conditions>";
if (ForceAssayId)
{
sql = sql.Replace("1=1", "<keyCriteria> = " + ForcedAssayId);
}
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
bool readOk = true;
int assayId = -1, currentAssayId = -1;
string assayType = "", assayMode = "";
while (true)
{
if (drd.Read())
{
assayId = drd.GetInt(0);
}
else
{
readOk = false;
}
if (assayId != currentAssayId || !readOk)
{
if (currentAssayId > 0)
{
assayTypeDict[currentAssayId] = assayType;
assayModeDict[currentAssayId] = assayMode;
assayType = assayMode = "";
}
if (!readOk)
{
break;
}
currentAssayId = assayId;
}
}
drd.CloseReader();
drd.Dispose();
return;
}
/// <summary>
/// Retrieve any existing SVG for the list of supplied molecules
/// The Id column should contain the CorpId
/// </summary>
/// <param name="molList"></param>
public static int SelectMoleculeListSvg(
List <MoleculeMx> molList)
{
MoleculeMx mol;
int corpId, molSvgsFetchedCount = 0;
string corpIdString, molString, svg;
const string sql = @"
SELECT
corp_nbr,
molstructure svgString
FROM
mbs_owner.corp_moltable_mx
WHERE
corp_nbr in (<list>)
and molstructure is not null
" ;
if (!Security.UserInfo.Privileges.CanRetrieveStructures) // structures allowed?
{
return(0);
}
//if (DebugMx.True) return 0; // debug, don't use existing values
List <string> lsnList = new List <string>();
Dictionary <string, MoleculeMx> molDict = new Dictionary <string, MoleculeMx>();
foreach (MoleculeMx mol0 in molList) // set up a dict keyed by cid with mol values
{
if (mol0.PrimaryFormat != MoleculeFormat.Helm || Lex.IsUndefined(mol0.PrimaryValue))
{
continue;
}
if (int.TryParse(mol0.Id, out corpId))
{
molDict[mol0.Id] = mol0;
}
}
if (molDict.Count == 0)
{
return(0);
}
DbCommandMx cmd = new DbCommandMx();
cmd.PrepareListReader(sql, DbType.String);
cmd.ExecuteListReader(new List <string>(molDict.Keys));
while (cmd.Read())
{
corpId = cmd.GetInt(0);
if (!cmd.IsNull(1)) // molstructure
{
molString = cmd.GetClob(1);
if (!SvgUtil.IsSvgString(molString))
{
continue; // skip if not SVG
}
svg = molString; // should be compressed format SVG
corpIdString = CompoundId.Normalize(corpId.ToString());
if (Lex.IsDefined(svg) && molDict.ContainsKey(corpIdString))
{
mol = molDict[corpIdString];
mol.SvgString = svg;
molSvgsFetchedCount++;
}
}
}
cmd.CloseReader();
return(molSvgsFetchedCount);
}
/// <summary>
/// Read in assay attribute information
/// </summary>
/// <returns></returns>
public static AssayDict ReadAssayAttributesTable()
{
int t0 = TimeOfDay.Milliseconds();
MetaTable mt = MetaTableCollection.Get(AssayAttrsTableName);
if (mt == null)
{
throw new Exception("Can't get table " + AssayAttrsTableName);
}
UnpivotedAssayResultFieldPositionMap voMap = new UnpivotedAssayResultFieldPositionMap(); // used for fast indexing of value by col name
string fList = "";
for (int mci = 0; mci < mt.MetaColumns.Count; mci++)
{
string colMap = mt.MetaColumns[mci].ColumnMap;
voMap.TrySetVoi(colMap, mci);
if (fList != "")
{
fList += ", ";
}
fList += colMap;
}
string sql =
"select " + fList + " " +
"from mbs_owner.cmn_assy_atrbts " +
"order by lower(gene_fmly), lower(gene_symbl), lower(assy_nm)";
// sql = AdjustAssayAttrsTableName(sql);
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
AssayDict dict = new AssayDict();
int readCnt = 0;
while (true)
{
if (!dao.Read())
{
break;
}
//if (readCnt > 100) break; // debug !!!
readCnt++;
object[] vo = new object[mt.MetaColumns.Count];
for (int mci = 0; mci < mt.MetaColumns.Count; mci++)
{
vo[mci] = dao.GetObject(mci);
}
AssayAttributes row = AssayAttributes.FromValueObjectNew(vo, voMap);
dict.AssayList.Add(row);
dict.SetMaps(row);
}
dao.CloseReader();
dao.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
return(dict);
}
/// <summary>
/// Get the next value for the sequence (MySQL)
/// </summary>
/// <param name="seqName"></param>
/// <returns></returns>
public static long NextValLongMySQL(
string seqName)
{
string sql;
long nextVal;
SequenceDao seqDao = Lookup(seqName);
Queue <long> seqQueue = seqDao.Queue;
if (seqQueue.Count > 0)
{
nextVal = seqQueue.Dequeue();
return(nextVal);
}
int count = (seqDao.CacheSize > 0 ? seqDao.CacheSize : 1);
int t0 = TimeOfDay.Milliseconds();
DbCommandMx seqCmd = new DbCommandMx();
seqCmd.MxConn = DbConnectionMx.GetConnection("MySql_Mobius"); // "MySql_Mobius_Sequences"
sql = String.Format(
@"update mbs_owner.mbs_sequences
set value = last_insert_id(value) + {0}
where name = '{1}'" , count, seqName.ToUpper());
seqCmd.PrepareUsingDefinedConnection(sql);
int updCount = seqCmd.ExecuteNonReader();
if (updCount <= 0)
{
throw new Exception("Error updating sequence (may not exist): " + seqName);
}
sql = "select last_insert_id()"; // gets value before update above
seqCmd.PrepareUsingDefinedConnection(sql, null);
DbDataReader rdr = seqCmd.ExecuteReader();
bool readOk = rdr.Read();
AssertMx.IsTrue(readOk, "readOk");
long value = rdr.GetInt64(0);
rdr.Close();
seqCmd.CloseReader();
nextVal = value + 1; // return this one now
long v2 = value + 2; // next value
long vn = value + count; // last value
for (long vi = v2; vi <= vn; vi++)
{
seqQueue.Enqueue(vi);
}
t0 = TimeOfDay.Milliseconds() - t0;
// DebugLog.Message("Read sequence, set size = " + seqQueue.Count.ToString() + ", Time(ms) = " + t0.ToString());
return(nextVal);
}
/// <summary>
/// SelectWithCriteria
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public static List <AfsLibrary> SelectWithCriteria(string criteria)
{
MetaTreeNode tempMtn = new MetaTreeNode(MetaTreeNodeType.Library);
StringBuilder sb = new StringBuilder(64);
string sql = @"
select
p.proj_id,
p.mbs_project_code,
proj_name,
p.mbs_dht_folder_code,
dht_folder_name,
platform_name,
lib_id,
lib_name,
lib_use
from
<mbs_owner>.afs_project p,
<mbs_owner>.afs_lib l
where
p.afs_current = 1
and p.mbs_project_code is not null
and l.afs_current = 1
and l.proj_id = p.proj_id
and <criteria>
order by upper(dht_folder_name), upper(proj_name), upper(lib_name)
" ;
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
sql = Lex.Replace(sql, "<criteria>", criteria);
List <AfsLibrary> libs = new List <AfsLibrary>();
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
string fileName = MetaTableFactory.MetaTableXmlFolder + @"\LibraryStats.txt";
Dictionary <string, MetaTableStats> libStats = new Dictionary <string, MetaTableStats>();
int libCount = MetaTableFactory.LoadMetaTableStats(fileName, libStats);
while (dao.Read())
{
AfsLibrary l = new AfsLibrary();
l.ProjId = dao.GetInt(0);
l.MbsProjectName = dao.GetString(1).ToUpper();
l.ProjectLabel = dao.GetString(2);
l.MbsDhtFolderName = dao.GetString(3).ToUpper();
l.DhtFolderLabel = dao.GetString(4).ToUpper();
l.Platform = dao.GetString(5).ToUpper();
l.LibId = dao.GetInt(6);
l.LibName = dao.GetString(7);
l.LibUse = dao.GetString(8).ToUpper();
string nodeName = "LIBRARY_" + l.LibId;
sb.Length = 0;
sb.Append(l.LibName);
sb.Append(" (");
if (!Lex.IsNullOrEmpty(l.LibUse))
{
sb.Append(l.LibUse);
sb.Append(", ");
}
sb.Append("Id: ");
sb.Append(l.LibId);
if (libStats.ContainsKey(nodeName))
{
tempMtn.Size = (int)libStats[nodeName].RowCount;
tempMtn.UpdateDateTime = libStats[nodeName].UpdateDateTime; // really create date
sb.Append(", ");
sb.Append(MetaTreeNode.FormatStatistics(tempMtn));
}
sb.Append(")");
l.LibLabel = sb.ToString();
libs.Add(l);
}
dao.CloseReader();
return(libs);
}