/// <summary> /// Calculate metatable statistics for each broker type /// </summary> /// <param name="factoryName"></param> /// <returns></returns> public static int UpdateStats( string factoryName) { int total = 0; if (Lex.Eq(factoryName, "Generic") || Lex.IsNullOrEmpty(factoryName)) { if (Instance == null) { Instance = new MetaTableFactory(); } total += Instance.UpdateMetaTableStatistics(); } for (int i1 = 0; i1 < MetaFactories.Count; i1++) { MetaTableFactoryRef mtfr = MetaFactories[i1]; if (!String.IsNullOrEmpty(factoryName) && Lex.Ne(mtfr.Name, factoryName)) { continue; // skip if name supplied and this isn't it } int cnt = mtfr.MetaTableFactory.UpdateMetaTableStatistics(); total += cnt; } return(total); }
/// <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"); }
Mfn.MetaTableFactory MetaTableFactoryInstance; // ref to real factory /// <summary> /// Constructor /// </summary> public MetaTableFactory() { if (ServiceFacade.UseRemoteServices) { //nothing to do } else { MetaTableFactoryInstance = new Mfn.MetaTableFactory(); } }
public MetaTable GetMetaTable( string mtName) { MetaTable mt = null; MetaColumn mc, mc2; int methodId; string txt, tok; mtName = mtName.Trim().ToUpper(); string name2 = mtName; string prefix = "ORACLE"; if (mtName.StartsWith(prefix + "_") || mtName.StartsWith(prefix + ".")) { name2 = mtName.Substring(prefix.Length + 1); } string[] sa = name2.Split('.'); if (sa.Length < 2) { return(null); } name2 = sa[0] + "." + sa[1]; mt = MetaTableFactory.GetMetaTableFromDatabaseDictionary(name2); if (mt == null || mt.MetaColumns.Count == 0) { return(null); } mt.Name = mtName; // assign fully qualified name if (sa.Length >= 3) // key col name included { mc = mt.GetMetaColumnByName(sa[2]); if (mc != null) { mc.DataType = MetaColumnType.CompoundId; } } if (sa.Length >= 4) // assign parent if parent table name included { mt.Parent = MetaTableCollection.Get(sa[3]); } return(mt); }
/// <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> /// Get metatable if name belongs to broker /// </summary> /// <param name="name"></param> /// <returns></returns> public MetaTable GetMetaTable( string name) { UserObject uo = null; int objectId, i1; string tok; Stopwatch sw = Stopwatch.StartNew(); if (!UserObject.IsAnnotationTableName(name) && !UserObject.IsUserDatabaseStructureTableName(name)) { return(null); } try { objectId = UserObject.ParseObjectIdFromInternalName(name); } catch (Exception ex) { return(null); } try { uo = UserObjectDao.Read(objectId); } catch (MobiusConnectionOpenException ex) { DbConnectionMx.ThrowSpecificConnectionOpenException(ex, "mbs_owner"); } if (uo == null) { return(null); } if (!Permissions.UserHasReadAccess(Security.UserName, uo)) { return(null); } MetaTable mt = MetaTable.Deserialize(uo.Content); if (mt == null) { return(null); // something wrong with the annotation table content } MetaColumn keyMc = mt.KeyMetaColumn; MetaColumn rootMc = mt.Root.KeyMetaColumn; if (rootMc.IsNumeric) // adjust key col mapping { keyMc.ColumnMap = "EXT_CMPND_ID_NBR"; } else { keyMc.ColumnMap = "EXT_CMPND_ID_TXT"; } while (mt.MetaColumns.Count > MetaTable.MaxColumns) // fixup for table with too many columns { mt.MetaColumns.RemoveAt(mt.MetaColumns.Count - 1); } foreach (MetaColumn mc in mt.MetaColumns) { // set DetailsAvailable for existing annotation tables */ if (mc.ResultCode != "") { mc.DetailsAvailable = true; } } mt.Label = uo.Name; // get latest name (may have changed if renamed after saved) if (MetaTableFactory.ShowDataSource) // add source to label if requested { mt.Label = MetaTableFactory.AddSourceToLabel(mt.Name, mt.Label); } long ms = sw.ElapsedMilliseconds; return(mt); }
private MetaTable GetMetaTableLevel2( string name) { UserObject uo; Query incQry = null; // included query QueryTable qt, qt1, qt2; QueryColumn qc, qc1, qc2; CalcFieldMetaTable mt; MetaTable mt1, mt2; MetaColumn mc, mc1, mc2, cfMc, cfMcModel; ExecuteQueryParms eqp; string cfLabel = "", mcName, mcLabel; string sql = null, tables = null, exprs = null, criteria = null; int objectId, startingSuffix, ci, i1; string prefix = "calcfield_"; // calculated field metatable names begin with "calcfield_" if (name.ToLower().IndexOf(prefix) != 0) { return(null); } if (Lex.Eq(name, "CALCFIELD_826476")) { name = name; // debug } string tok = name.Substring(prefix.Length); // get the object id try { objectId = Int32.Parse(tok); } catch (Exception ex) { return(null); } uo = UserObjectDao.Read(objectId); if (uo == null) { return(null); } if (!Permissions.UserHasReadAccess(Security.UserName, uo)) { return(null); } CalcField cf = CalcField.Deserialize(uo.Content); if (cf == null) { return(null); } if (cf.FinalResultType == MetaColumnType.Unknown) { return(null); // need a final result type } mt = new CalcFieldMetaTable(); mt.Name = name; mt.CalcField = cf; // include associated CalcField object mt.Label = uo.Name; if (MetaTableFactory.ShowDataSource) // add source to label if requested { mt.Label = MetaTableFactory.AddSourceToLabel(mt.Name, mt.Label); } mt.Description = cf.Description; mt.MetaBrokerType = MetaBrokerType.CalcField; bool keyAdded = false; cfLabel = cf.ColumnLabel; // get any user defined label for result // Add metacolumns for basic calculated field if (cf.CalcType == CalcTypeEnum.Basic && cf.Column1.MetaColumn != null) { cfMcModel = cf.Column1.MetaColumn; tok = cf.Operation; if (Lex.IsNullOrEmpty(cfLabel)) { if (tok.IndexOf("/") == 0) { cfLabel = "Ratio"; } else if (tok.IndexOf("*") == 0) { cfLabel = "Product"; } else if (tok.IndexOf("+") == 0) { cfLabel = "Sum"; } else if (tok.IndexOf("-") == 0) { cfLabel = "Difference"; } else { if (cf.Column1.FunctionEnum == CalcFuncEnum.None) // just use existing col name { cfLabel = cf.Column1.MetaColumn.Label; } else // use function & col name { tok = cf.Column1.Function; i1 = tok.IndexOf("("); if (i1 >= 0) { tok = tok.Substring(0, i1); } if (!tok.EndsWith(" ")) { tok += " "; } tok += cf.MetaColumn1.Label; cfLabel = tok; } } if (cf.IsClassificationDefined) { cfLabel += " Class"; // add class suffix if classifier } } } else if (cf.CalcType == CalcTypeEnum.Advanced && cf.AdvancedExpr != "") { List <MetaColumn> mcList = cf.GetInputMetaColumnList(); if (mcList.Count == 0) { return(null); } cfMcModel = mcList[0]; if (cfLabel == "") { cfLabel = "Value"; } } else { return(null); } mt.Parent = cfMcModel.MetaTable.Parent; // set parent if (mt.Root == mt) { mt.Parent = cfMcModel.MetaTable; // if F1 table is a root set it as parent } // Add key value mc = cfMcModel.MetaTable.KeyMetaColumn; // make copy of first table key field for our key if (mc == null) { return(null); } mc = mc.Clone(); mc.ColumnMap = mc.Name; // map to self mc.MetaTable = mt; mt.AddMetaColumn(mc); keyAdded = true; mc.IsSearchable = cf.IsSearchable; // Add calculated value column mc = new MetaColumn(); mt.AddMetaColumn(mc); mc.MetaTable = mt; mc.Name = "CALC_FIELD"; mc.Label = cfLabel; mc.DataType = cf.FinalResultType; // final result type mc.InitialSelection = ColumnSelectionEnum.Selected; mc.Format = cfMcModel.Format; mc.Width = cfMcModel.Width; mc.Decimals = cfMcModel.Decimals; if (cf.FinalResultType == MetaColumnType.Integer) { mc.Format = ColumnFormatEnum.Decimal; mc.Decimals = 0; } if (cf.FinalResultType == MetaColumnType.Image) { mc.Width = (int)(mc.Width * 2.0); // make CF images wider } mc.IsSearchable = cfMcModel.IsSearchable; if (mc.IsSearchable) // refine searchability { mc.IsSearchable = cf.IsSearchable; } cfMc = mc; // Add metacolumns for the underlying columns that go into the calculation foreach (CalcFieldColumn cfc in cf.CfCols) { if (cfc.MetaColumn == null) { continue; } if (cfc.MetaColumn.IsKey) { continue; // don't add additional key } mc = cfc.MetaColumn.Clone(); mcName = mc.MetaTable.Name + "_" + mc.Name; mc.Name = mt.GetValidMetaColumnName(mcName); mc.DetailsAvailable = false; // no drill-down for now (can cause issues) mc.IsSearchable = false; // no searching for now (can cause issues) mc.ColumnMap = mc.Name; // map to self if (Lex.Eq(mc.Label, cfLabel)) // if same as result start suffix checking at 2 { startingSuffix = 1; } else { startingSuffix = 2; } mc.Label = mt.GetUniqueMetaColumnLabel(mc.Label, startingSuffix); mc.InitialSelection = ColumnSelectionEnum.Hidden; // hide for now mc.TableMap = cfc.MetaColumn.MetaTable.Name + "." + cfc.MetaColumn.Name; // save source table and column //mc.TableMap = cfc.MetaColumn.MetaTable.Name; // save source table and column //mc.ColumnMap = cfc.MetaColumn.Name; mt.AddMetaColumn(mc); } //mt.MetaColumns.Remove(cfMc); // move cf result column to end after the inputs //mt.MetaColumns.Add(cfMc); return(mt); }
static Dictionary <string, ColumnSelectionEnum> DisplayLevelOverride; // override values for display levels from metafactories public MetaTableFactory() { Instance = this; }
/// <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); }
/// <summary> /// Create MetaTable for a PubChem assay /// </summary> public MetaTable GetMetaTable( string name) { MetaTable mt = null, mt2 = null, mt3 = null; MetaColumn mc, mcc; string txt, tok; string aid; string prefix = "pubchem_aid_"; if (!name.ToLower().StartsWith(prefix)) { return(null); } int t0 = TimeOfDay.Milliseconds(); aid = name.Substring(prefix.Length); XmlNode node; XmlNodeList nodes; TextReader rdr = GetAssayXml(aid); if (rdr == null) { return(null); } XmlDocument doc = new XmlDocument(); doc.Load(rdr); mt = new MetaTable(); mt.Name = "PUBCHEM_AID_" + aid.ToString(); mt.TableMap = "(select * " + "from mbs_owner.mbs_pbchm_rslt)"; mt.MetaBrokerType = MetaBrokerType.Pivot; mt.Parent = MetaTableCollection.Get("pubchem_structure"); mt.TableFilterColumns = Csv.SplitCsvString("mthd_vrsn_id"); mt.TableFilterValues = Csv.SplitCsvString(aid); mt.PivotMergeColumns = Csv.SplitCsvString("ext_cmpnd_id_nbr, rslt_grp_id"); mt.PivotColumns = Csv.SplitCsvString("rslt_typ_id"); XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable); nsmgr.AddNamespace("ab", "http://www.ncbi.nlm.nih.gov"); // must look within this namespace MetaTreeNode mtn = MetaTreeFactory.GetNode(mt.Name); // try to get label from contents tree mtn = null; if (mtn != null) { mt.Label = mtn.Label; } else { string dataSource = SelectNodeValue(doc, nsmgr, "//ab:PC-DBTracking_name"); string assayName = SelectNodeValue(doc, nsmgr, "//ab:PC-AssayDescription_name"); string outcomeMethod = SelectNodeValue(doc, nsmgr, "//ab:PC-AssayDescription_activity-outcome-method"); mt.Label = assayName; if (outcomeMethod == "1") { outcomeMethod = "Primary"; } else if (outcomeMethod == "2") { outcomeMethod = "Dose-Response"; } else if (outcomeMethod == "3") { outcomeMethod = "Probe Summary"; } else { outcomeMethod = ""; } if (outcomeMethod.Length > 0) { mt.Label += " (" + outcomeMethod + ")"; } } if (mt.Label == "") { mt.Label = mt.Name; } mt.Description = // reference PubChem description page "http://pubchem.ncbi.nlm.nih.gov/assay/assay.cgi?aid=" + aid; nodes = doc.SelectNodes("//ab:PC-ResultType", nsmgr); // result types // Build common metacolumns mc = mt.AddMetaColumn("PUBCHEM_CID", "CID", MetaColumnType.CompoundId, ColumnSelectionEnum.Selected, 14); mc.ColumnMap = "ext_cmpnd_id_nbr"; mc = mt.AddMetaColumn("PUBCHEM_SID", "SID", MetaColumnType.Integer, ColumnSelectionEnum.Selected, 7); mc.ColumnMap = "rslt_val_nbr"; mc.PivotValues = Csv.SplitCsvString("1001"); // assign codes that won't interfere with assay-specific codes mc = mt.AddMetaColumn("PUBCHEM_ACTIVITY_SCORE", "Activity Score", MetaColumnType.Integer, ColumnSelectionEnum.Selected, 7); mc.ColumnMap = "rslt_val_nbr"; mc.PivotValues = Csv.SplitCsvString("1002"); mc = mt.AddMetaColumn("PUBCHEM_ACTIVITY_OUTCOME", "Activity Outcome", MetaColumnType.DictionaryId, ColumnSelectionEnum.Selected, 7); mc.ColumnMap = "rslt_val_nbr"; mc.Dictionary = "PubChemActivityOutcome"; // code stored in db must be translated via dictionary mc.PivotValues = Csv.SplitCsvString("1003"); mc = mt.AddMetaColumn("PUBCHEM_ACTIVITY_URL", "Activity URL", MetaColumnType.Hyperlink, ColumnSelectionEnum.Selected, 7); mc.ColumnMap = "rslt_val_txt"; mc.PivotValues = Csv.SplitCsvString("1004"); // Build assay-specific metacolumns for (int ci = 0; ci < nodes.Count; ci++) { node = nodes[ci]; string typeId = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_tid"); // get values relative to node (need "." in xpath string) string typeName = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_type"); string colName = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_name"); string desc = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_description_E"); if (desc.IndexOf(", unit: ") > 0) // shorten label a bit { desc = desc.Replace(", unit: ", " ("); if (desc.EndsWith(".")) { desc = desc.Substring(0, desc.Length - 1); } desc += ")"; } string unit = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_unit"); if (unit == "") { unit = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_sunit"); } if (Lex.Eq(unit, "none") || Lex.Eq(unit, "unspecified")) { unit = ""; } if (unit.EndsWith("ml")) { unit = unit.Substring(0, unit.Length - 2) + "/mL"; } string transform = SelectNodeValue(node, nsmgr, ".//ab:PC-ResultType_transform"); mc = new MetaColumn(); mc.PivotValues = Csv.SplitCsvString(typeId); mc.Name = "R_" + typeId; mc.Label = colName; if (unit != "") { mc.Label += " (" + unit + ")"; } if (mc.Label == "") { mc.Label = mc.Name; } //mc.Label = desc; // user longer description for label //if (mc.Label == "") mc.Label = mc.Label; mc.Format = ColumnFormatEnum.Default; mc.Decimals = -1; mc.InitialSelection = ColumnSelectionEnum.Selected; if (Lex.Eq(typeName, "float")) { mc.DataType = MetaColumnType.Number; mc.Width = 8; mc.ColumnMap = "rslt_val_nbr"; } else if (Lex.Eq(typeName, "int")) { mc.DataType = MetaColumnType.Number; mc.Width = 8; mc.Format = ColumnFormatEnum.Decimal; mc.Decimals = 0; mc.ColumnMap = "rslt_val_nbr"; } else if (Lex.Eq(typeName, "bool")) { mc.DataType = MetaColumnType.Number; mc.Width = 8; mc.ColumnMap = "rslt_val_nbr"; } else if (Lex.Eq(typeName, "string")) { mc.DataType = MetaColumnType.String; mc.Width = 8; mc.ColumnMap = "rslt_val_txt"; } else { throw new Exception("Invalid type" + typeName); } mc.MetaTable = mt; mt.AddMetaColumn(mc); } mc = mt.AddMetaColumn("PUBCHEM_ASSAYDATA_COMMENT", "Comment", MetaColumnType.String, ColumnSelectionEnum.Unselected, 7); mc.ColumnMap = "rslt_val_txt"; mc.PivotValues = Csv.SplitCsvString("1005"); mc = mt.AddMetaColumn("PUBCHEM_EXT_DATASOURCE_REG", "Ext. Source. Reg. Id", MetaColumnType.String, ColumnSelectionEnum.Unselected, 7); mc.ColumnMap = "rslt_val_txt"; mc.PivotValues = Csv.SplitCsvString("1006"); mc = mt.AddMetaColumn("PUBCHEM_ASSAYDATA_REVOKE", "Revoked", MetaColumnType.String, ColumnSelectionEnum.Hidden, 7); mc.ColumnMap = "rslt_val_txt"; mc.PivotValues = Csv.SplitCsvString("1007"); MetaTableFactory.SetAnyNewMetaTableLabel(mt); // if table has been renamed the set new label foreach (MetaColumn mc2 in mt.MetaColumns) { MetaTableFactory.SetAnyNewMetaColumnLabel(mc2); // if col has been renamed then set new label } GetMetaTableCount++; t0 = TimeOfDay.Milliseconds() - t0; GetMetaTableTime += t0; GetMetaTableAvgTime = GetMetaTableTime / GetMetaTableCount; return(mt); }