/// <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>
/// Setup/configure any needed secondary metabroker
/// </summary>
/// <param name="eqp"></param>
/// <param name="mpd"></param>
/// <param name="unpivotedTableIsFirst"></param>
void SetupSecondaryMetaBroker(
ExecuteQueryParms eqp,
MultiTablePivotBrokerTypeData mpd,
out bool unpivotedTableIsFirst)
{
QueryEngine qe2;
Query q2;
QueryTable qt2;
QueryColumn qc2;
MetaTable mt2;
string firstMtName = mpd.FirstTableName;
MetaTable firstMt = MetaTableCollection.GetWithException(firstMtName);
unpivotedTableIsFirst = UnpivotedAssayResult.IsUnpivotedSummarizedMdbAssayTable(firstMtName);
if (unpivotedTableIsFirst) // just use unpivoted table as is
{
SecondaryMetaBroker = this;
Eqp2 = eqp;
}
else // all pivoted tables, create secondary query on the summarized unpivoted table
{
if (SecondaryMetaBroker == null)
{
MultiDbAssayMetaBroker mb2 = SecondaryMetaBroker = new MultiDbAssayMetaBroker();
if (UnpivotedAssayResult.IsCombinedMdbAssayTable(firstMtName))
{
mt2 = MetaTableCollection.GetWithException(MultiDbAssayDataNames.CombinedTableName);
}
else
{
mt2 = MetaTableCollection.GetWithException(MultiDbAssayDataNames.BaseTableName);
}
q2 = new Query();
qt2 = new QueryTable(mt2);
qc2 = qt2.GetQueryColumnByNameWithException(MultiDbAssayDataNames.GeneSymbol);
if (mpd.TableCodeCsvList.Length != 0) // limit by codes
{
qc2.Criteria = qc2.CriteriaDisplay = MultiDbAssayDataNames.GeneSymbol + " in (" + mpd.TableCodeCsvList + ")";
}
q2.AddQueryTable(qt2);
qe2 = new QueryEngine();
qe2.Query = q2;
Eqp2 = new ExecuteQueryParms(qe2, qt2);
mb2.PrepareQuery(Eqp2);
mb2.Sql += " where " + qc2.Criteria;
}
Eqp2.SearchKeySubset = eqp.SearchKeySubset;
}
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);
}
