/// <summary>
/// Get the list, returns array of AssayIDs
/// </summary>
/// <returns></returns>
///
public IList GetUnpivotedAssayResultsDataSource()
{
StreamReader sr;
bool changed;
int i1;
if (AssayId != null)
{
return(AssayId);
}
// Read cache parameters
changed = ServerFile.GetIfChanged(ServerCacheDir + "CacheParms.txt", ClientCacheDir + "CacheParms.txt");
sr = new StreamReader(ClientCacheDir + "CacheParms.txt");
string txt = sr.ReadLine();
ResultsRowCount = int.Parse(txt);
sr.Close();
// Init Vo position tables
TarFieldPositionMap = UnpivotedAssayResultFieldPositionMap.NewOriginalMap(); // used for fast indexing of value by col name
QueryTable qt = Query.GetQueryTableByName(MultiDbAssayDataNames.CombinedNonSumTableName);
if (qt != null)
{
TarFieldPositionMap.InitializeFromQueryTableVoPositions(qt, 0);
}
// Read in list of assay Ids
AssayId = ReadUShortArray("AssayId.bin"); // read in the list of assay ids for starters
return(AssayId);
}
/// <summary>
/// Summarize unpivoted unsummarized assay data by target
/// </summary>
/// <param name="parms"></param>
/// <param name="qm2"></param>
/// <returns></returns>
public QueryManager SummarizeByTargetUnpivoted(
TargetSummaryOptions summaryOptions,
QueryManager qm2)
{
// This method takes an unpivoted input query, summarizes it according to the summarization parameters
// and then formats the summarized data according to the specified output format.
// If a targetMap is specified then then coordinates are included in the output, the summarization
// level must be target, and the output format must be unpivoted.
QueryTable qt, qt2;
DataRowMx dr, dr2;
DataRowAttributes dra;
string cid = "", currentCid;
int rti, rfi;
AssayDict tad = TargetAssayDict; // be sure target assay dict has been loaded
TargetAssaySummarizationLevel sumLevel = TargetAssaySummarizationLevel.Target; // target level
SummarizationType sumMethod = summaryOptions.UseMeans ? SummarizationType.BioResponseMean : SummarizationType.MostPotent;
OutputDest outputDest = OutputDest.WinForms;
TargetMap targetMap = TargetMapDao.GetMapWithCoords(summaryOptions.TargetMapName);
qt = Query.GetQueryTableByName(MultiDbAssayDataNames.CombinedNonSumTableName);
if (qt == null)
{
throw new Exception("Query table not found: " + MultiDbAssayDataNames.CombinedNonSumTableName);
}
UnpivotedAssayResultFieldPositionMap voMap = UnpivotedAssayResultFieldPositionMap.NewOriginalMap(); // used for fast indexing of value by col name
voMap.InitializeFromQueryTableVoPositions(qt, 0);
if (qm2 == null) // need to create query manager?
{
qm2 = new QueryManager();
qm2 = InitializeSubqueryQm(MultiDbAssayDataNames.CombinedNonSumTableName);
}
Query q2 = qm2.Query;
qt2 = q2.GetQueryTableByNameWithException(MultiDbAssayDataNames.BaseTableName);
UnpivotedAssayResultFieldPositionMap voMap2 = UnpivotedAssayResultFieldPositionMap.NewOriginalMap(); // used for fast indexing of value by col name
voMap2.InitializeFromQueryTableVoPositions(qt2, 0);
// Summarize rows & store in DataSet
qm2.DataTable.Clear();
Dictionary <string, object> includedTargets = null;
if (summaryOptions.TargetsWithActivesOnly)
{ // scan data & make a list of targets to be included
includedTargets = new Dictionary <string, object>();
// ...
}
List <UnpivotedAssayResult> tars = new List <UnpivotedAssayResult>(); // build list of TA rows here
currentCid = "";
for (int dri = 0; dri <= DataTableMx.Rows.Count; dri++)
{
if (dri < DataTableMx.Rows.Count)
{
dr = DataTableMx.Rows[dri];
cid = dr[KeyValueVoPos] as string;
dra = GetRowAttributes(dr);
if (dra != null && dra.Filtered)
{
continue;
}
if (currentCid == "")
{
currentCid = cid;
}
}
else
{
dr = null;
}
if (dr == null || cid != currentCid)
{ // summarize rows for current cid & add to new datatable
if (tars.Count > 0) //
{
List <UnpivotedAssayResult> sumTars = TargetAssayUtil.SummarizeData(
tars,
sumLevel,
sumMethod,
true,
NullValue.NullNumber,
NullValue.NullNumber,
targetMap);
int voLength2 = qm2.DataTable.Columns.Count;
foreach (UnpivotedAssayResult sumTar in sumTars)
{
object[] vo2 = sumTar.ToValueObject(voLength2, voMap2);
dr2 = qm2.DataTable.NewRow();
dr2.ItemArrayRef = vo2; // copy ref for efficiency since vo won't be changed
qm2.DataTable.Rows.Add(dr2);
}
}
if (dr == null)
{
break;
}
tars.Clear();
currentCid = cid;
}
UnpivotedAssayResult tar = UnpivotedAssayResult.FromValueObjectNew(dr.ItemArray, voMap);
tars.Add(tar); // store in form for summarization
}
qm2.DataTableManager.InitializeRowAttributes();
return(qm2);
}
/// <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>
/// Execute query in preparation for retrieving rows
/// </summary>
/// <param name="parms"></param>
public override void ExecuteQuery(
ExecuteQueryParms eqp)
{
BufferedRows = new List <UnpivotedAssayResult>();
MultiTablePivotBrokerTypeData mpd = null;
MetaTable mt;
List <MultiDbAssayMetaBroker> mbList = null;
MultiDbAssayMetaBroker mb, mb2 = null;
UnpivotedAssayResult rr, rr2;
List <UnpivotedAssayResult> rrList = new List <UnpivotedAssayResult>(); // list of results summarized by target & result type
bool unpivotedTableIsFirst;
object[] vo;
string mtName = null;
int t0 = TimeOfDay.Milliseconds();
mt = eqp.QueryTable.MetaTable;
Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = eqp.Qe.MetaBrokerStateInfo;
if (PivotInCode)
{
mpd = mbsi[MpGroupKey];
if (PivotInCode && mpd.MbInstances.Count == 1 && UnpivotedAssayResult.IsUnpivotedSummarizedMdbAssayTable(mt.Name))
{
PivotInCode = false; // don't multipivot if single unpivoted summary table
}
}
if (!PivotInCode) // if not multipivot then call generic broker
{
base.ExecuteQuery(eqp);
return;
}
if (mpd.FirstTableName != Qt.MetaTable.Name)
{
return; // retrieve data for all tables when we see first table
}
SetupSecondaryMetaBroker(eqp, mpd, out unpivotedTableIsFirst);
mb2 = SecondaryMetaBroker;
mpd.ClearBuffers();
// Retrieve data & store for associated metabrokers
if (mb2 == this)
{
base.ExecuteQuery(eqp); // execute with the base generic broker
}
else
{
mb2.ExecuteQuery(Eqp2); // use the secondary broker that was created
}
AssayDict dict = new AssayDict();
rr = new UnpivotedAssayResult();
int readCnt = 0;
bool includeResultDetail = MdbAssayVoMap.ResultDetailId.Voi >= 0;
while (true)
{
if (mb2 == this)
{
vo = base.NextRow(); // get data via generic broker
}
else
{
vo = mb2.NextRow(); // get data with secondary broker
}
if (vo == null)
{
break;
}
rr.FromValueObject(vo, mb2.MdbAssayVoMap); // parse values into a UnpivotedAssayResult
int rowsFetched = 0, vosCreated = 0;
for (int pup = 0; pup < 2; pup++) // first pass for unpivoted table, 2nd for pivoted by gene
{
//try
//{
if (pup == 0)
{
if (!unpivotedTableIsFirst)
{
continue; // if no unpivoted first table skip this
}
mtName = mpd.FirstTableName; // unpivoted table should be first
}
else // pivoted table
{
if (Lex.IsNullOrEmpty(rr.GeneSymbol))
{
continue; // skip if no target symbol
}
if (Lex.Contains(mpd.FirstTableName, "CORP")) // mapped to pivoted corp only table
{
mtName = MultiDbAssayDataNames.BasePivotTablePrefix + rr.GeneSymbol.ToUpper(); // name of table mapped to
}
else // combined tables
{
mtName = MultiDbAssayDataNames.CombinedPivotTablePrefix + rr.GeneSymbol.ToUpper();
}
}
//}
//catch (Exception ex) { ex = ex; }
mt = MetaTableCollection.Get(mtName);
if (mt == null)
{
continue;
}
if (!mpd.MbInstances.ContainsKey(mt.Name))
{
continue; // have row hash for broker?
}
int mbIdx = 0;
if (mpd.MbInstances[mtName] is MultiDbAssayMetaBroker)
{
mb = (MultiDbAssayMetaBroker)mpd.MbInstances[mtName]; // broker assoc w/table
}
else
{
mbList = (List <MultiDbAssayMetaBroker>)mpd.MbInstances[mtName];
mb = (MultiDbAssayMetaBroker)mbList[0];
}
while (true) // copy out for each metabroker for metatable
{
UnpivotedAssayResultFieldPositionMap voMap = mb.MdbAssayVoMap;
vo = rr.ToValueObject(mb.Qt.SelectedCount, voMap);
if (mb.MultipivotRowList == null)
{
mb.MultipivotRowList = new List <object[]>();
}
mb.MultipivotRowList.Add(vo);
if (mbList == null)
{
break; // single broker
}
mbIdx++; // go to next broker
if (mbIdx >= mbList.Count)
{
break; // at end of brokers?
}
mb = (MultiDbAssayMetaBroker)mbList[mbIdx];
}
} // tables to copy data to loop
} // row fetch loop
return;
}
/// <summary>
/// Prepare query
/// </summary>
/// <param name="parms"></param>
public override string PrepareQuery(
ExecuteQueryParms eqp)
{
List <MultiDbAssayMetaBroker> mbList;
MultiTablePivotBrokerTypeData mpd; // multipivot data for this broker type
Dictionary <string, MultiTablePivotBrokerTypeData> mbsi;
QueryColumn qc;
MetaTable mt;
Eqp = eqp;
Qt = eqp.QueryTable;
mt = eqp.QueryTable.MetaTable;
BuildActivityBinCondFormat();
MdbAssayVoMap = UnpivotedAssayResultFieldPositionMap.NewMdbAssayMap(Qt);
MdbAssayVoMap.InitializeForQueryTable(Qt);
LoadTargetMap();
if ( // check for basic conditions that disallow multipivoting
!eqp.ReturnQNsInFullDetail || // no multipivot if part of calc field
eqp.Qe == null || // need to be able to access queryengine info
//!QueryEngine.AllowMultiTablePivot || // is multipivot even allowed
!UnpivotedAssayResult.IsSummarizedMdbAssayTable(mt.Name)) // summarized tables only
{
return(base.PrepareQuery(eqp));
}
int pivotedColCount = 0;
foreach (QueryColumn qc1 in Qt.QueryColumns)
{ // if any non-key criteria then pivot individually rather than via multipivot
if (qc1.Criteria != "" && !qc1.IsKey)
{
if (UnpivotedAssayResult.IsUnpivotedSummarizedMdbAssayTable(mt.Name) && Qt.Query.SingleStepExecution)
{
} // special case: allow criteria on unpivoted summary table which also apply to associated pivoted tables
else
{
return(base.PrepareQuery(eqp));
}
}
if (IsPivotedColumn(qc1.MetaColumn))
{
pivotedColCount++;
}
}
if (pivotedColCount == 0)
{
return(base.PrepareQuery(eqp)); // must have at least one column to pivot
}
// Store pivot info for queryTable
PivotInCode = true;
Sql = BuildSql(eqp);
if (eqp.Qe.MetaBrokerStateInfo == null)
{
eqp.Qe.MetaBrokerStateInfo = new Dictionary <string, MultiTablePivotBrokerTypeData>();
}
mbsi = eqp.Qe.MetaBrokerStateInfo;
MpGroupKey = MetaBrokerType.TargetAssay.ToString(); // key for broker for query
if (!QueryEngine.AllowMultiTablePivot)
{
MpGroupKey += "_" + Qt.MetaTable.Name;
}
mpd = MultiTablePivotBrokerTypeData.GetMultiPivotData(eqp.Qe.MetaBrokerStateInfo, MpGroupKey, mt.Name);
string geneSymbol = mt.Code;
if (!Lex.IsNullOrEmpty(geneSymbol) && !mpd.TableCodeDict.ContainsKey(geneSymbol))
{
mpd.TableCodeDict[geneSymbol] = new MpdResultTypeData(); // add key to hash list
if (mpd.TableCodeCsvList.Length > 0)
{
mpd.TableCodeCsvList.Append(",");
}
mpd.TableCodeCsvList.Append(Lex.AddSingleQuotes(geneSymbol));
}
mpd.AddMetaBroker(mt.Name, this);
return(Sql);
}
