/// <summary>
/// Insert a row into the target-assay association table
/// </summary>
/// <param name="toks"></param>
/// <param name="dao"></param>
public static void InsertCommonAssayAttributes(
AssayAttributes taa,
DbCommandMx dao)
{
string names = "";
string values = "";
AddInsertColumn("id", ref names, ref values, taa.Id);
AddInsertColumn("assy_db", ref names, ref values, taa.AssayDatabase);
AddInsertColumn("assy_id2", ref names, ref values, taa.AssayId2);
AddInsertColumn("assy_id_nbr", ref names, ref values, taa.AssayIdNbr);
AddInsertColumn("assy_id_txt", ref names, ref values, taa.AssayIdTxt);
AddInsertColumn("assy_nm", ref names, ref values, taa.AssayName);
AddInsertColumn("assy_desc", ref names, ref values, taa.AssayDesc);
AddInsertColumn("assy_src", ref names, ref values, taa.AssaySource);
AddInsertColumn("assy_typ", ref names, ref values, taa.AssayType);
AddInsertColumn("assy_mode", ref names, ref values, taa.AssayMode);
AddInsertColumn("assy_sts", ref names, ref values, taa.AssayStatus);
AddInsertColumn("assy_sum_lvl", ref names, ref values, taa.SummarizedAvailable ? 1 : 0);
AddInsertColumn("assy_gene_cnt", ref names, ref values, taa.GeneCount);
AddInsertColumn("rslt_typ", ref names, ref values, taa.ResultTypeConcType);
AddInsertColumn("rslt_typ_nm", ref names, ref values, taa.ResultName);
AddInsertColumn("rslt_typ_id2", ref names, ref values, taa.ResultTypeId2);
AddInsertColumn("rslt_typ_id_nbr", ref names, ref values, taa.ResultTypeIdNbr);
AddInsertColumn("rslt_typ_id_txt", ref names, ref values, taa.ResultTypeIdTxt);
AddInsertColumn("rslt_uom", ref names, ref values, taa.ResultTypeUnits);
AddInsertColumn("conc_uom", ref names, ref values, taa.ResultTypeConcUnits);
AddInsertColumn("top_lvl_rslt", ref names, ref values, taa.TopLevelResult);
AddInsertColumn("remppd", ref names, ref values, taa.Remapped);
AddInsertColumn("mltplxd", ref names, ref values, taa.Multiplexed);
AddInsertColumn("rvwd", ref names, ref values, taa.Reviewed);
AddInsertColumn("cmpds_assyd", ref names, ref values, taa.CompoundsAssayed);
AddInsertColumn("prflng_assy", ref names, ref values, taa.ProfilingAssay);
AddInsertColumn("gene_id", ref names, ref values, taa.GeneId);
AddInsertColumn("gene_symbl", ref names, ref values, taa.GeneSymbol);
AddInsertColumn("gene_desc", ref names, ref values, taa.GeneDescription);
AddInsertColumn("gene_fmly", ref names, ref values, taa.GeneFamily);
AddInsertColumn("rslt_cnt", ref names, ref values, taa.ResultCount);
AddInsertColumn("assy_updt_dt", ref names, ref values, taa.AssayUpdateDate);
AddInsertColumn("assn_src", ref names, ref values, taa.AssociationSource);
AddInsertColumn("assn_cnflct", ref names, ref values, taa.AssociationConflict);
AddInsertColumn("x", ref names, ref values, taa.TargetMapX);
AddInsertColumn("y", ref names, ref values, taa.TargetMapY);
AddInsertColumn("z", ref names, ref values, taa.TargetMapZ);
string sql = "insert into mbs_owner.cmn_assy_atrbts (" + names + ") " +
"values (" + values + ")";
// sql = AssayAttributesDao.AdjustAssayAttrsTableName(sql);
dao.Prepare(sql);
dao.ExecuteNonReader();
return;
}
/// <summary>
/// Update Common Assay Attributes table (mbs_owner.cmn_assy_atrbts)
///
/// Command: Update AssayAttributesTable
///
/// This command builds an entry (or two) in the cmn_assy_atrbts table for each assay
/// referenced in the Mobius contents tree that reports an SP or CRC value as determined
/// by the associated metafactory and available in each metatable.
/// If the gene target associated with an assay can be identified then information on that
/// gene is included as well.
///
/// Additional gene information may come from the metadata for a source such as the results
/// warehouse.
///
/// Note that this function must be run under an account that has access to all restricted data so that it
/// can properly see what's available and build the table.
/// </summary>
/// <param name="lex"></param>
/// <returns></returns>
public static string UpdateAssayAttributesTable(string args)
{
MetaTable mt;
MetaColumn mc;
AssayAttributes aa, aa2;
Dictionary <int, int> geneIdCounts = new Dictionary <int, int>(); // genes and counts keyed by entrez gene id
Dictionary <int, double> targetMapXDict; // dendogram X coord keyed by gene id
Dictionary <int, double> targetMapYDict; // dendogram Y coord keyed by gene id
Dictionary <string, int> targetTypeCounts = new Dictionary <string, int>(); // target types and counts
Dictionary <int, string> assayTypeDict;
Dictionary <int, string> assayModeDict;
//UnpivotedAssayResult rr, rr2;
List <string> toks;
string mtName, tableNamePrefix, key, fileName, msg;
bool crcExists, spExists, isSummary;
int tableId, step, ri, resultTypeId, assayRowCount;
Log = new LogFile(ServicesDirs.LogDir + @"\UpdateCommonAssayAttributes.log");
Log.ResetFile();
// Get list of all of the assays in the tree
LogMessage("Accumulating assays...");
HashSet <string> mtNameHash = new HashSet <string>();
foreach (MetaTreeNode mtn0 in MetaTree.Nodes.Values)
{
if (mtn0.Type != MetaTreeNodeType.MetaTable)
{
continue;
}
mtName = mtn0.Target;
if (AssayMetaData.IsAssayMetaTableName(mtName))
{
mtNameHash.Add(mtName);
}
}
bool debug = false; // set to true to debug with limited list of assays from below
if (debug)
{
mtNameHash.Clear();
//assayHash.Add("ASSAY_1");
//assayHash.Add("ASSAY_2");
//assayHash.Add("ASSAY_3");
}
// Get other informatin needed from AssayMetadata
LogMessage("Reading AssayMetadata ASSAY metadata...");
Dictionary <int, AssayDbMetadata> assayMetadataAssayDict = // get assays and associated target/gene information
AssayMetadataDao.GetAssayTargetGeneData();
LogMessage("Getting AssayMetadata result types...");
Dictionary <int, AssayDbResultType> resultTypeDict = AssayMetadataDao.GetResultTypeDict();
LogMessage("Getting assay types and modes...");
AssayMetadataDao.GetAssayTypesAndModes(out assayTypeDict, out assayModeDict);
LogMessage("Getting gene dendogram coordinates...");
try
{
AssayMetadataDao.GetImageCoords(out targetMapXDict, out targetMapYDict);
}
catch (Exception ex) // may fail if problem with data source
{
LogMessage(DebugLog.FormatExceptionMessage(ex, true));
targetMapXDict = new Dictionary <int, double>();
targetMapYDict = new Dictionary <int, double>();
}
// Process each assay
int metatablesFound = 0, metatablesNotFound = 0;
int assaysProcessed = 0;
int assaysWithGenes = 0;
int assaysWithGeneCoords = 0;
int assaysWithTargets = 0;
int assaysWithSpOnly = 0;
int assaysWithCrcOnly = 0;
int assaysWithNoCrcSP = 0;
int assaysWithOtherTypes = 0;
int assaysWithCrcAndSp = 0;
int assaysWithNoKeyTypes = 0;
int assaysWithProcessingErrors = 0;
Dictionary <string, int> CrcAssayCnt = new Dictionary <string, int>()
{
{ "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
};
Dictionary <string, int> SpAssayCnt = new Dictionary <string, int>()
{
{ "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
};
Dictionary <string, int> OtherAssayCnt = new Dictionary <string, int>()
{
{ "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
};
List <AssayAttributes> resultTypeRows = new List <AssayAttributes>();
List <AssayAttributes> geneRows = new List <AssayAttributes>();
List <AssayAttributes> dbRows = new List <AssayAttributes>();
string copyUpdateMsg = "";
foreach (string mtName0 in mtNameHash)
{
AssayMetaData assayMetaData = null; // metadata for assay
bool isAssay = false;
int assayIdNbr = NullValue.NullNumber;
int assayId = NullValue.NullNumber;
//if (assaysProcessed >= 100) break; // debug
mtName = mtName0;
MetaTable.ParseMetaTableName(mtName, out tableNamePrefix, out tableId, out isSummary);
string resultType = "";
string rtId = "";
string assayName, assayDb;
mt = MetaTableCollection.Get(mtName); // get metatable
if (mt == null)
{
metatablesNotFound++;
LogMessage("MetaTable not found: " + mtName);
continue;
}
metatablesFound++;
if (mt.Code == "")
{
continue; // must be single pivoted assay
}
assayDb = "ASSAY_DB"; // customize
assayIdNbr = -1;
if (UalUtil.IClient != null && UalUtil.IClient.Attended)
{
UAL.Progress.Show((assaysProcessed + 1).ToString() + " / " + mtNameHash.Count + " - " + mt.Name + "\r\n" + mt.Label);
}
aa = new AssayAttributes();
aa.AssayDatabase = assayDb;
aa.AssayIdNbr = assayIdNbr; // data-source-specific assay Id
aa.AssayIdTxt = mt.Name; // store ASSAY_1234 type table name
aa.AssayId2 = assayId; // any associated assay id
if (isAssay)
{
aa.AssayName = assayMetaData.Name; // name from AssayMetadata
}
else
{
aa.AssayName = MetaTable.RemoveSuffixesFromName(mt.Label); // name from metatable
}
if (mt.SummarizedExists)
{
aa.SummarizedAvailable = true;
}
else
{
aa.SummarizedAvailable = false;
}
if (isAssay)
{
if (assayTypeDict.ContainsKey(tableId))
{
aa.AssayType = assayTypeDict[tableId];
}
if (assayModeDict.ContainsKey(tableId))
{
aa.AssayMode = assayModeDict[tableId];
}
aa.AssaySource = AssayMetaData.GetAssaySource(tableId);
aa.AssociationSource = "TODO"; // customize
}
aa.AssayStatus = "Active"; // say all active for now
MetaTableStats mts = MetaTableFactory.GetStats(mtName);
if (mts != null)
{
aa.ResultCount = (int)mts.RowCount;
aa.AssayUpdateDate = mts.UpdateDateTime;
}
else
{
aa.ResultCount = 0; // assume no results if no stats
}
if (mt.DescriptionIsAvailable()) // use description from Mobius
{
aa.AssayDesc = "Y";
}
if (String.IsNullOrEmpty(aa.GeneFamily))
{
aa.GeneFamily = "Unknown"; // set these to "Unknown" rather than null
}
if (String.IsNullOrEmpty(aa.AssayType))
{
aa.AssayType = "UNKNOWN"; // upper case UNKNOWN
}
if (String.IsNullOrEmpty(aa.AssayMode))
{
aa.AssayMode = "UNKNOWN"; // upper case UNKNOWN
}
// Step1: Add a row for primary & any secondary results
resultTypeRows.Clear();
MetaColumn firstResultCol = null, firstKeyResultCol = null, firstOtherKeyResultCol = null;
string resultTypeConcType;
HashSet <string> keyResultTypeCodes = new HashSet <string>();
int spCnt = 0, crcCnt = 0, otherCnt = 0;
for (int mci = 0; mci < mt.MetaColumns.Count; mci++) // pick first col with result code (could also check summarization method)
{
mc = mt.MetaColumns[mci];
if (Lex.IsUndefined(mc.ResultCode))
{
continue; // must have code defined
}
if (keyResultTypeCodes.Contains(mc.ResultCode))
{
continue; // and not included so far
}
if (mc.InitialSelection != ColumnSelectionEnum.Selected)
{
continue; // selected only
}
if (firstResultCol == null)
{
firstResultCol = mc;
}
if (!IsKeyResultType(mc, out resultTypeConcType))
{
continue;
}
if (firstKeyResultCol == null)
{
firstKeyResultCol = mc;
}
keyResultTypeCodes.Add(mc.ResultCode);
aa2 = aa.Clone();
if (resultTypeRows.Count == 0)
{
aa2.TopLevelResult = "Y";
}
else
{
aa2.TopLevelResult = "N";
}
aa2.ResultTypeId2 = GetAssayResultTypeId(mc); // AssayMetadata result type id
aa2.ResultTypeIdNbr = GetInternalResultTypeId(mc); // Internal database result type id
aa2.ResultTypeIdTxt = mc.Name; // Mobius column name
if (isAssay && resultTypeDict.ContainsKey(aa2.ResultTypeId2))
{
aa2.ResultName = resultTypeDict[aa2.ResultTypeId2].Name; // use name from AssayMetadata result type dict
}
else
{
aa2.ResultName = mc.Label; // use label from Mobius
}
aa2.ResultTypeUnits = mc.Units; // result units
if (Lex.Eq(resultTypeConcType, "SP"))
{
aa2.ResultTypeConcType = "SP";
spCnt++;
}
else if (Lex.Eq(resultTypeConcType, "CRC"))
{
aa2.ResultTypeConcType = "CRC";
crcCnt++;
}
else
{
aa2.ResultTypeConcType = "";
otherCnt++;
if (firstOtherKeyResultCol == null)
{
firstOtherKeyResultCol = mc;
}
}
aa2.ResultTypeConcUnits = ""; // todo
resultTypeRows.Add(aa2);
}
if (resultTypeRows.Count >= 1)
{
if (crcCnt > 0)
{
CrcAssayCnt[assayDb]++; // count primary type by db
}
else if (spCnt > 0)
{
SpAssayCnt[assayDb]++;
}
else
{
OtherAssayCnt[assayDb]++;
}
if (crcCnt > 0 && spCnt == 0)
{
assaysWithCrcOnly++; // count overall primary/secondary types
}
else if (crcCnt == 0 && spCnt > 0)
{
assaysWithSpOnly++;
}
else if (crcCnt > 0 && spCnt > 0)
{
assaysWithCrcAndSp++;
}
if (crcCnt == 0 && spCnt == 0) // no SP or CRC result types
{
assaysWithNoCrcSP++;
mc = firstKeyResultCol;
LogMessage("Assay with No SP/CRC key results: " + mt.Name + "." + mc.Name + " (" + mc.ResultCode + "), " + mt.Label + "." + mc.Label);
}
else if (otherCnt > 0) // no SP or CRC result types
{
assaysWithOtherTypes++;
mc = firstOtherKeyResultCol;
LogMessage("Non SP/CRC key result: " + mt.Name + "." + mc.Name + " (" + mc.ResultCode + "), " + mt.Label + "." + mc.Label);
}
}
else // no key result type
{
aa2 = aa.Clone();
resultTypeRows.Add(aa2); // include row for step1
OtherAssayCnt[assayDb]++;
assaysWithNoKeyTypes++;
LogMessage("No key result type for metatable: " + mt.Name + ", " + mt.Label);
}
// Build a step2 row for each target/gene
geneRows.Clear();
List <AssayTarget> targets = new List <AssayTarget>();
int geneCount = 0;
if (isAssay)
{
targets = assayMetaData.Targets;
}
if (targets.Count > 0)
{
assaysWithTargets++;
}
foreach (AssayTarget target in targets)
{
aa = new AssayAttributes();
aa.GeneFamily = target.TargetTypeShortName; // count target type occurance
if (Lex.IsUndefined(aa.GeneFamily))
{
aa.GeneFamily = "Unknown";
}
if (!targetTypeCounts.ContainsKey(aa.GeneFamily))
{
targetTypeCounts[aa.GeneFamily] = 0;
}
targetTypeCounts[aa.GeneFamily]++;
if (target.Genes == null || target.Genes.Count == 0) // if no genes add a single target row
{
geneRows.Add(aa);
continue;
}
foreach (AssayGene rg in target.Genes)
{
if (!Lex.IsDefined(rg.GeneSymbol))
{
continue;
}
aa2 = aa.Clone();
geneRows.Add(aa2);
aa2.GeneSymbol = rg.GeneSymbol;
int.TryParse(rg.GeneId, out aa2.GeneId);
if (aa2.GeneId > 0 && targetMapXDict.ContainsKey(aa2.GeneId))
{
aa2.TargetMapX = targetMapXDict[aa2.GeneId];
aa2.TargetMapY = targetMapYDict[aa2.GeneId];
if (geneCount == 0)
{
assaysWithGeneCoords++;
}
}
if (!geneIdCounts.ContainsKey(aa2.GeneId)) // count gene occurance
{
geneIdCounts[aa2.GeneId] = 0;
}
geneIdCounts[aa2.GeneId]++;
if (geneCount == 0)
{
assaysWithGenes++;
}
geneCount++;
}
}
if (geneRows.Count == 0) // if no step 2 rows (i.e. no targets), create a single step2 row
{
aa = new AssayAttributes();
geneRows.Add(aa);
}
// Combine key result types with target/genes
for (int i1 = 0; i1 < resultTypeRows.Count; i1++)
{
AssayAttributes s1aa = resultTypeRows[i1];
for (int i2 = 0; i2 < geneRows.Count; i2++)
{
AssayAttributes s2aa = geneRows[i2];
aa = s1aa.Clone();
aa.GeneId = s2aa.GeneId;
aa.GeneSymbol = s2aa.GeneSymbol;
aa.GeneFamily = s2aa.GeneFamily;
aa.TargetMapX = s2aa.TargetMapX;
aa.TargetMapY = s2aa.TargetMapY;
aa.GeneCount = geneCount;
if (i2 > 0)
{
aa.GeneCount = -geneCount; // negative for other than 1st gene
}
dbRows.Add(aa);
}
}
assaysProcessed++;
}
// Update table
bool updateTable = true; // set to false for debug
if (dbRows.Count <= 0)
{
LogMessage("No rows in new dataset, table not updated");
}
else if (updateTable)
{
LogMessage("Deleting existing data...");
DbCommandMx dao = new DbCommandMx();
string sql = "delete from mbs_owner.cmn_assy_atrbts";
sql = AssayAttributesDao.AdjustAssayAttrsTableName(sql);
dao.Prepare(sql);
dao.BeginTransaction();
int delCnt = dao.ExecuteNonReader();
LogMessage("Inserting new data...");
int t0 = TimeOfDay.Milliseconds();
for (ri = 0; ri < dbRows.Count; ri++)
{
aa = dbRows[ri];
aa.Id = ri + 1;
//aa.Id += 10000; // debug
if (aa.GeneSymbol != null)
{
aa.GeneSymbol = aa.GeneSymbol.ToUpper(); // be sure key match cols are upper case
}
if (aa.GeneFamily != null)
{
aa.GeneFamily = aa.GeneFamily.ToUpper();
}
if (aa.GeneFamilyTargetSymbol != null)
{
aa.GeneFamilyTargetSymbol = aa.GeneFamilyTargetSymbol.ToUpper();
}
if (aa.ResultTypeConcType != null)
{
aa.ResultTypeConcType = aa.ResultTypeConcType.ToUpper();
}
if (aa.AssayType != null)
{
aa.AssayType = aa.AssayType.ToUpper();
}
if (aa.AssayMode != null)
{
aa.AssayMode = aa.AssayMode.ToUpper();
}
AssayAttributesDao.InsertCommonAssayAttributes(aa, dao);
if (TimeOfDay.Milliseconds() - t0 > 1000)
{
//Progress.Show("Inserting new data " + (ri + 1) + "/" + rows.Count + "...");
t0 = TimeOfDay.Milliseconds();
}
}
dao.Commit();
dao.Dispose();
copyUpdateMsg = UpdateCmnAssyAtrbtsCopies();
}
string response =
"----------------------------------\r\n" +
"Assays processed: " + assaysProcessed + "\r\n" +
"Assays with processing errors: " + assaysWithProcessingErrors + "\r\n" +
"Rows inserted: " + dbRows.Count + "\r\n" +
copyUpdateMsg +
"----------------------------------\r\n" +
"Assays with CRC only: " + assaysWithCrcOnly + "\r\n" +
"Assays with SP only: " + assaysWithSpOnly + "\r\n" +
"Assays with CRC and SP: " + assaysWithCrcAndSp + "\r\n" +
"Assays with no CRC or SP: " + assaysWithNoCrcSP + "\r\n" +
"Assays with non CRC/SP key types: " + assaysWithOtherTypes + "\r\n" +
"Assays with no key types: " + assaysWithNoKeyTypes + "\r\n" +
"----------------------------------\r\n" +
"Assays with targets defined: " + assaysWithTargets + "\r\n" +
"Assays with genes defined: " + assaysWithGenes + "\r\n" +
"Assays with gene map coordinates: " + assaysWithGeneCoords + "\r\n" +
"----------------------------------\r\n" +
//"CRC Assays: " + CrcAssayCnt["ASSAY"] + "\r\n" +
//"SP Assays: " + SpAssayCnt["ASSAY"] + "\r\n" +
//"??? Assays: " + OtherAssayCnt["ASSAY"] + "\r\n" +
"----------------------------------";
LogMessage("\r\n" + response);
UAL.Progress.Hide();
return(response);
}
/// <summary>
/// Execute query
/// </summary>
/// <param name="eqp"></param>
public override void ExecuteQuery(
ExecuteQueryParms eqp)
{
MetaTable mt;
MetaColumn mc;
Query q;
QueryTable qt;
QueryColumn qc;
ResultsPage rp;
ResultsViewProps view;
CompoundStructureActivityData cd1, cd2;
bool smallerIsbetter;
double r1, r2, r3, r4;
int di, di2, pdi, pdi2, i3;
string tok;
qt = eqp.QueryTable;
qc = qt.GetQueryColumnByNameWithException(SasMapParms.ParametersMetaColumnName);
AssertMx.IsDefined(qc.Criteria, qc.Label + " criteria not defined");
if (Lex.Eq(qc.Criteria, LastCriteriaString)) // if same criteria as last time then use existing data
{
VoListPos = -1; // init list position
return;
}
VoList = new List<object[]>();
VoListPos = -1; // init list position
LastCriteriaString = qc.Criteria;
ParsedSingleCriteria psc = ParsedSingleCriteria.Parse(qc);
SMP = SasMapParms.Deserialize(psc.Value);
mc = SMP.EndpointMc;
smallerIsbetter = mc.MultiPoint;
List<CompoundStructureActivityData> ds1 = ReadData(SMP); // read in the data to analyze
if (ds1 == null || ds1.Count == 0) return; // throw new QueryException("No data retrieved");
List<CompoundStructureActivityData> ds2 = ds1; // just one set for now
// Calculate difference or ratio coefficents for each pair
List<PairData> pd = new List<PairData>();
int minCoef = -1; // index of minimum coefficent selected so far
double molFactor = AssayAttributes.GetMolarConcFactor(SMP.EndpointMc);
for (di = 0; di < ds1.Count; di++)
{ // process all compounds in 1st set
// if (ds1[di].Nearest == 0) continue; // any data?
if (ds2 == ds1) di2 = di + 1; // only do lower rt diagonal if one dataset
else di2 = 0; // must do all compares, check for dups later
for ( /* start at di2 */; di2 < ds2.Count; di2++)
{
// if (ds2[di2].Nearest == 0) continue; // any data?
if (ds1[di].Cid == ds2[di2].Cid) continue; // avoid self compare
double sim = // similarity
CalculateSimilarity(ds1[di], ds2[di2]);
//if (sim==1.0 && !stereo) // eliminate stereo pairs if requested
// continue; // a more careful check may be needed
if (sim < SMP.MinimumSimilarity) continue; // below cutoff value?
double denom = 1 - sim; // denominator is 1 - sim
if (denom == 0) denom = .00000000001f; // avoid divide by zero
double actChange = 0;
if (smallerIsbetter && ds1[di].Activity < ds2[di2].Activity)
{
cd1 = ds1[di];
cd2 = ds2[di2];
}
else
{
cd1 = ds2[di2];
cd2 = ds1[di];
}
double a1 = cd1.Activity;
double a2 = cd2.Activity;
if (a1 == NullValue.NullNumber || a2 == NullValue.NullNumber)
actChange = 0;
else switch (SMP.ActDiffCalcType)
{
case ActDiffCalcType.SimpleDifference: // activity difference
{
actChange = a1 - a2;
break;
}
case ActDiffCalcType.NegativeLog:
{
actChange = -Math.Log10(a1) - -Math.Log10(a2);
break;
}
case ActDiffCalcType.MolarNegativeLog:
{
actChange = (-Math.Log10(a1 * molFactor)) - (-Math.Log10(a2 * molFactor));
break;
}
case ActDiffCalcType.Ratio: // activity ratio
{
r1 = a1;
if (r1 == 0) r1 = .00000000001f;
r2 = a2;
if (r2 == 0) r2 = .00000000001f;
r3 = r1 / r2;
r4 = r2 / r1;
actChange = r3;
if (SMP.UseAbsoluteValue && r4 > r3) // take the max value
actChange = r4;
break;
}
case ActDiffCalcType.Advanced:
{
throw new InvalidOperationException("SarMapCalcType.Advanced");
}
default:
throw new InvalidOperationException("SarMapCalcType: " + (int)SMP.ActDiffCalcType);
}
if (SMP.UseAbsoluteValue && SMP.ActDiffCalcType != ActDiffCalcType.Ratio)
actChange = Math.Abs(actChange);
double coef = actChange / denom;
if (pd.Count < SMP.MaxPairCount) // just add this pair to end
{
pdi = pd.Count;
pd.Add(new PairData());
}
else
{ // see if this value is greater than anything in list
if (minCoef < 0)
{ // find element with minimum coef
minCoef = 0;
for (i3 = 1; i3 < pd.Count; i3++)
{
if (pd[i3].Coef < pd[minCoef].Coef)
minCoef = i3;
}
}
if (coef <= pd[minCoef].Coef) continue; // if this one better?
//if (ds1 != ds2)
//{ // be sure not a duplicate of what we have in list
// for (i3 = 0; i3 < pd.Count; i3++)
// { // check for pair in list already
// if ((di == pd[i3].CD1 && di2 == pd[i3].CD2) ||
// (di == pd[i3].CD2 && di2 == pd[i3].CD1)) break;
// }
// if (i3 < pd.Count) continue; // continue to next pair if found
//}
pdi = minCoef; // replace this item
minCoef = -1; // reset to get new minimum next time
}
// Save data for the pair
pd[pdi].CD1 = cd1;
pd[pdi].CD2 = cd2;
pd[pdi].Sim = sim;
pd[pdi].ActChange = actChange;
pd[pdi].Coef = coef;
}
}
// Build the list of pair Vos
int voLen = qt.SetSimpleVoPositions();
PairData pdItem;
for (pdi = 1; pdi < pd.Count; pdi++) // sort from max to min coef value
{
pdItem = pd[pdi];
for (pdi2 = pdi - 1; pdi2 >= 0; pdi2--)
{
if (pdItem.Coef < pd[pdi2].Coef) break;
pd[pdi2 + 1] = pd[pdi2];
}
pd[pdi2 + 1] = pdItem;
}
for (pdi = 0; pdi < pd.Count; pdi++)
{
pdItem = pd[pdi];
cd1 = pdItem.CD1;
cd2 = pdItem.CD2;
object[] vo = new object[voLen];
VoArray.SetVo(qt, "PAIR_ID", vo, new NumberMx(pdi + 1));
VoArray.SetVo(qt, "COMPOUND1", vo, new StringMx(cd1.Cid));
VoArray.SetVo(qt, "STRUCTURE1", vo, cd1.Structure);
VoArray.SetVo(qt, "ACTIVITY1", vo, new NumberMx(cd1.Activity));
VoArray.SetVo(qt, "COMPOUND2", vo, new StringMx(cd2.Cid));
VoArray.SetVo(qt, "STRUCTURE2", vo, cd2.Structure);
VoArray.SetVo(qt, "ACTIVITY2", vo, new NumberMx(cd2.Activity));
VoArray.SetVo(qt, "SIMILARITY", vo, new NumberMx(pdItem.Sim));
VoArray.SetVo(qt, "ACTIVITY_DIFF", vo, new NumberMx(pdItem.ActChange));
VoArray.SetVo(qt, "ACT_SIM_COEF", vo, new NumberMx(pdItem.Coef));
VoList.Add(vo);
}
VoListPos = -1; // init list position
return;
}
/// <summary>
/// Join an unpivoted target-summarized table to itself
/// </summary>
public QueryManager BuildTargetTargetData(
QueryManager qm2)
{
QueryTable qt, qt2;
DataRowMx dr, dr2;
DataRowAttributes dra, dra2;
string cid = "", cid2 = "", currentCid, ttKey;
int rti, rfi;
qt = Query.GetQueryTableByNameWithException(MultiDbAssayDataNames.BaseTableName); // source table
UnpivotedAssayResultFieldPositionMap voMap = UnpivotedAssayResultFieldPositionMap.NewOriginalMap(); // used for fast indexing of value by col name
voMap.InitializeFromQueryTableVoPositions(qt, 0);
if (qm2 == null || Math.Abs(1) == 1) // need to create query manager? (needs fixup)
{
qm2 = new QueryManager();
qm2 = InitializeSubqueryQm(MultiDbAssayDataNames.TargetTargetUnpivotedTableName);
}
Query q2 = qm2.Query;
qt2 = q2.GetQueryTableByNameWithException(MultiDbAssayDataNames.TargetTargetUnpivotedTableName);
// Join rows & store in DataSet
qm2.DataTable.Clear();
Dictionary <string, int> ttDict = new Dictionary <string, int>(); // maps target pair to row
Dictionary <string, List <string> > cidTargetDict = new Dictionary <string, List <string> >();
for (int dri = 0; dri < DataTableMx.Rows.Count; dri++)
{ // get list of targets for each cid
dr = DataTableMx.Rows[dri];
cid = dr[KeyValueVoPos] as string;
dra = GetRowAttributes(dr);
if (dra != null && dra.Filtered)
{
continue;
}
object[] vo = dr.ItemArrayRef;
if (!cidTargetDict.ContainsKey(cid))
{
cidTargetDict[cid] = new List <string>();
}
string target = AssayAttributes.GetStringVo(vo, voMap.TargetSymbol.Voi);
cidTargetDict[cid].Add(target);
}
foreach (string cid0 in cidTargetDict.Keys)
{ // sum count for pair of targets
List <string> targets = cidTargetDict[cid0];
for (int t1i = 0; t1i < targets.Count; t1i++)
{
string t1 = targets[t1i];
for (int t2i = t1i; t2i < targets.Count; t2i++)
{
string t2 = targets[t2i];
if (Lex.Lt(t1, t2))
{
ttKey = t1 + '\t' + t2;
}
else
{
ttKey = t2 + '\t' + t1;
}
if (!ttDict.ContainsKey(ttKey))
{
ttDict[ttKey] = 0;
}
ttDict[ttKey]++;
}
}
}
foreach (string ttkey0 in ttDict.Keys)
{ // build data rows
string[] t1t2 = ttkey0.Split('\t');
string t1 = t1t2[0];
string t2 = t1t2[1];
int cpdCount = ttDict[ttkey0];
AddRow(qm2.DataTable, t1, t2, cpdCount);
if (t2 != t1)
{
AddRow(qm2.DataTable, t2, t1, cpdCount);
}
}
qm2.DataTableManager.InitializeRowAttributes();
return(qm2);
}
/// <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 a primitive value from the in-memory Target Assay Results cache
/// </summary>
/// <param name="dri"></param>
/// <param name="rfld"></param>
/// <returns></returns>
public object GetPrimitiveValueFromTarCache(
int dri,
ResultsField rfld)
{
AssayAttributes taa = null;
BinaryReader br;
long streamLength;
string s;
int rowNum;
GetPrimitiveValueFromTarCacheCalls++;
ushort aid = AssayId[dri];
if (aid == LastAid)
{
taa = LastTaa;
}
else if (TargetAssayDict.AssayIdMap.ContainsKey(aid)) // link to associated target-assay attributes
{
LastAid = aid;
LastTaa = taa = TargetAssayDict.AssayIdMap[aid];
}
int voi = rfld.VoPosition;
if (voi < 0 || voi >= TarFieldPositionMap.VoiToTarColEnum.Length)
{
return(null); // not in range
}
TarColEnum tarCol = TarFieldPositionMap.VoiToTarColEnum[voi];
switch (tarCol)
{
// Assay Attributes - Get from TargetAssayDictionary
case TarColEnum.Id:
if (taa != null)
{
return(taa.Id);
}
else
{
return(null);
}
case TarColEnum.TargetSymbol:
if (taa != null)
{
return(taa.GeneSymbol);
}
else
{
return(null);
}
case TarColEnum.TargetId:
if (taa != null)
{
return(taa.GeneId);
}
else
{
return(null);
}
case TarColEnum.TargetDescription:
if (taa != null)
{
return(taa.GeneDescription);
}
else
{
return(null);
}
case TarColEnum.GeneFamily:
if (taa != null)
{
return(taa.GeneFamily);
}
else
{
return(null);
}
case TarColEnum.GeneFamilyTargetSymbol: // catenation of target family & gene symbol
if (taa != null)
{
return(taa.GeneFamilyTargetSymbol);
}
else
{
return(null);
}
case TarColEnum.AssayDatabase:
if (taa != null)
{
return(taa.AssayDatabase);
}
else
{
return(null);
}
case TarColEnum.AssayMetaTableName: // name of the metatable containing the data
if (taa != null)
{
return(taa.AssayMetaTableName);
}
else
{
return(null);
}
case TarColEnum.AssayName:
if (taa != null)
{
return(taa.AssayName);
}
else
{
return(null);
}
case TarColEnum.AssayNameSx: // contains name & link when deserialized from StringEx
if (taa != null)
{
return(taa.AssayName); // todo: add link
}
else
{
return(null);
}
case TarColEnum.AssayDesc:
if (taa != null)
{
return(taa.AssayDesc);
}
else
{
return(null);
}
case TarColEnum.AssayLocation:
if (taa != null)
{
return(taa.AssaySource);
}
else
{
return(null);
}
case TarColEnum.AssayType:
if (taa != null)
{
return(taa.AssayType);
}
else
{
return(null);
}
case TarColEnum.AssayMode:
if (taa != null)
{
return(taa.AssayMode);
}
else
{
return(null);
}
case TarColEnum.AssayStatus:
if (taa != null)
{
return(taa.AssayStatus);
}
else
{
return(null);
}
case TarColEnum.AssayGeneCount:
if (taa != null)
{
return(taa.GeneCount);
}
else
{
return(null);
}
case TarColEnum.ResultName: // result label
if (taa != null)
{
return(taa.ResultName);
}
else
{
return(null);
}
case TarColEnum.ResultTypeIdTxt: // text form of result type code
if (taa != null)
{
return(taa.ResultTypeIdTxt);
}
else
{
return(null);
}
case TarColEnum.ResultTypeId2: // numeric form of result type code:
if (taa != null)
{
return(taa.ResultTypeId2);
}
else
{
return(null);
}
case TarColEnum.ResultTypeConcType: // SP or CRC
if (taa != null)
{
return(taa.ResultTypeConcType);
}
else
{
return(null);
}
case TarColEnum.TopLevelResult: // Y if this is a CRC or if a SP with no associated CRC
if (taa != null)
{
return(taa.TopLevelResult);
}
else
{
return(null);
}
case TarColEnum.Remapped:
if (taa != null)
{
return(taa.Remapped);
}
else
{
return(null);
}
case TarColEnum.Multiplexed:
if (taa != null)
{
return(taa.Multiplexed);
}
else
{
return(null);
}
case TarColEnum.Reviewed:
if (taa != null)
{
return(taa.Reviewed);
}
else
{
return(null);
}
case TarColEnum.ProfilingAssay:
if (taa != null)
{
return(taa.ProfilingAssay);
}
else
{
return(null);
}
case TarColEnum.CompoundsAssayed:
if (taa != null)
{
return(taa.CompoundsAssayed);
}
else
{
return(null);
}
case TarColEnum.ResultCount:
if (taa != null)
{
return(taa.ResultCount);
}
else
{
return(null);
}
case TarColEnum.AssayUpdateDate: // date of most recent assay result
if (taa != null)
{
return(taa.AssayUpdateDate);
}
else
{
return(null);
}
case TarColEnum.AssociationSource: // ASSAY2EG
if (taa != null)
{
return(taa.AssociationSource);
}
else
{
return(null);
}
case TarColEnum.AssociationConflict: // conflicting source & it's assignment
if (taa != null)
{
return(taa.AssociationConflict);
}
else
{
return(null);
}
case TarColEnum.TargetMapX:
if (taa != null)
{
return(taa.TargetMapX);
}
else
{
return(null);
}
case TarColEnum.TargetMapY:
if (taa != null)
{
return(taa.TargetMapY);
}
else
{
return(null);
}
case TarColEnum.TargetMapZ:
if (taa != null)
{
return(taa.TargetMapZ);
}
else
{
return(null);
}
// Results - Get from in-memory arrays, one per table column
case TarColEnum.CompoundId:
if (CompoundId == null)
{
CompoundId = ReadIntArray("CompoundId.bin");
}
return(CompoundId[dri]);
case TarColEnum.AssayId2: // numeric form of assay/table identifier
return((int)AssayId[dri]); // should already have data AssayId array
case TarColEnum.AssayIdTxt: // text form of assay/table identifier
return(AssayId.ToString());
//case TarColEnum.ResultTypeIdTxt: // text form of result type code
// if (ResultTypeId == null)
// ResultTypeId = ReadUShortArray("ResultTypeId.bin");
// return ResultTypeId[dri].ToString(); // get from results data array
//case TarColEnum.ResultTypeIdNbr: // numeric form of result type identifier
// if (ResultTypeId == null)
// ResultTypeId = ReadUShortArray("ResultTypeId.bin");
// return (int)ResultTypeId[dri]; // get from results data array
case TarColEnum.ActivityBin:
if (ActivityBin == null)
{
ActivityBin = ReadByteArray("ActivityBin.bin");
}
return((int)ActivityBin[dri]);
case TarColEnum.ResultValue: // just return numeric value
if (ResultNumericValue == null)
{
ResultNumericValue = ReadSingleArray("ResultNumericValue.bin");
}
return(ResultNumericValue[dri]);
case TarColEnum.ResultQualifier:
if (ResultQualifier == null)
{
ResultQualifier = ReadByteArray("ResultQualifier.bin");
}
if (ResultQualifier[dri] != ' ')
{
return(ResultQualifier[dri].ToString());
}
else
{
return("");
}
case TarColEnum.ResultNumericValue:
if (ResultNumericValue == null)
{
ResultNumericValue = ReadSingleArray("ResultNumericValue.bin");
}
return(ResultNumericValue[dri]);
case TarColEnum.ResultTextValue:
if (ResultTextValue == null)
{
ResultTextValue = new Dictionary <int, string>();
br = OpenBinaryReader("ResultTextValue.bin");
streamLength = br.BaseStream.Length;
while (true)
{
if (br.BaseStream.Position >= streamLength - 1)
{
break;
}
rowNum = br.ReadInt32();
s = br.ReadString();
ResultTextValue[rowNum] = s;
}
br.Close();
}
if (ResultTextValue.ContainsKey(dri))
{
return(ResultTextValue[dri]);
}
else
{
return("");
}
case TarColEnum.NValue:
return(0);
case TarColEnum.NValueTested:
return(0);
case TarColEnum.StdDev:
return(0);
case TarColEnum.Units:
if (Units == null)
{
Units = new Dictionary <int, string>();
br = OpenBinaryReader("Units.bin");
streamLength = br.BaseStream.Length;
while (true)
{
if (br.BaseStream.Position >= streamLength - 1)
{
break;
}
rowNum = br.ReadInt32();
s = br.ReadString();
Units[rowNum] = s;
}
br.Close();
}
if (ResultTextValue.ContainsKey(dri))
{
return(ResultTextValue[dri]);
}
else
{
return("");
}
case TarColEnum.Conc:
if (Conc == null)
{
ReadConcData();
}
if (Conc.ContainsKey(dri))
{
return(Conc[dri]);
}
else
{
return(0);
}
case TarColEnum.ConcUnits:
if (ConcUnits == null)
{
ReadConcData();
}
if (ConcUnits.ContainsKey(dri))
{
return(ConcUnits[dri]);
}
else
{
return("");
}
case TarColEnum.RunDate:
if (RunDate == null)
{
RunDate = ReadUShortArray("RunDate.bin");
}
ushort days = RunDate[dri];
DateTime dt = BaseDate.AddDays(days);
return(dt);
case TarColEnum.ResultDetail:
return("");
default:
throw new Exception("Unexpected TarColEnum: " + tarCol);
}
}