/// <summary>
/// Build the sql for a query
/// </summary>
/// <param name="eqp">ExecuteQueryParms</param>
/// <returns>Sql for the query</returns>
public override string BuildSql(
ExecuteQueryParms eqp)
{
string sql = "";
MetaColumn mc;
List <MetaBrokerType> MbTypes = // metabrokers that can provide unpivoted assay data
new List <MetaBrokerType>();
MbTypes.Add(MetaBrokerType.Assay);
Eqp = eqp;
Qt = eqp.QueryTable;
Exprs = FromClause = OrderBy = ""; // outer sql elements
KeyMc = Mt.KeyMetaColumn;
SelectList = new List <MetaColumn>(); // build list of selected metacolumns
foreach (QueryColumn qc in Qt.QueryColumns)
{
mc = qc.MetaColumn;
if (qc.Selected)
{
SelectList.Add(mc);
}
}
foreach (MetaBrokerType mbt in MbTypes) // get Sql for each broker and union together
{
IMetaBroker mb = MetaBrokerUtil.Create(mbt);
if (mb == null)
{
throw new Exception("Unrecognized Metabroker: " + mbt);
}
string sql2 = mb.BuildUnpivotedAssayResultsSql(eqp);
//if (actBinAQc != null)
// sql2 = Lex.Replace(sql2, "null activity_bin", ActivityBinSqlExpression + " activity_bin");
if (Lex.IsDefined(sql))
{
sql += " union all ";
}
sql += "/*** MetaBrokerType." + mbt.ToString() + " ***/ " + sql2;
}
sql = " select * from ( " + sql + " ) ";
if (Qt.Alias != "")
{
sql += " " + Qt.Alias;
}
if (eqp.CallerSuppliedCriteria != "")
{
sql += " where " + eqp.CallerSuppliedCriteria;
}
return(sql);
}
/// <summary>
/// Build summarization detail query
/// </summary>
/// <param name="mt"></param>
/// <param name="mc"></param>
/// <param name="level"></param>
/// <param name="linkInfo"></param>
/// <returns></returns>
public override Query GetDrilldownDetailQuery(
MetaTable mt,
MetaColumn mc,
int level,
string linkInfo)
{
QueryTable qt, qt2;
MetaTable mt2;
QueryColumn qc, qc2;
MetaColumn mc2;
string resultIdColName = "";
Query q = new Query();
// Summarized MdbAssay table drilling down to unsummarized MdbAssay table
if (UnpivotedAssayResult.IsSummarizedMdbAssayTable(mt.Name))
{
if (UnpivotedAssayResult.IsCombinedMdbAssayTable(mt.Name)) // multiple tables
{
mt2 = MetaTableCollection.GetWithException(MultiDbAssayDataNames.CombinedNonSumTableName);
resultIdColName = MultiDbAssayDataNames.SumResultId;
}
else // base unpivoted or pivoted by target
{
mt2 = MetaTableCollection.GetWithException(MultiDbAssayDataNames.BaseNonSumTableName);
resultIdColName = MultiDbAssayDataNames.BaseSumResultId;
}
qt = new QueryTable(q, mt2);
qc = qt.GetQueryColumnByNameWithException(resultIdColName);
qc.Criteria = resultIdColName + " = " + linkInfo;
return(q);
}
// Drilling down from old all_bioassay_unpivoted to specific source data
else if (Lex.Eq(mt.Name, UnpivotedAssayView.UnsummarizedMetaTableName))
{ // ResultId is formatted by the source with the first item being the source name
Match m = Regex.Match(linkInfo, @"(.*),(.*),(.*)", RegexOptions.IgnoreCase | RegexOptions.RightToLeft);
string mtName = m.Groups[1].Value;
string mcId = m.Groups[2].Value;
string linkInfo2 = m.Groups[3].Value;
mt2 = MetaTableCollection.GetWithException(mtName);
IMetaBroker imb = MetaBrokerUtil.GlobalBrokers[(int)mt2.MetaBrokerType];
q = imb.GetDrilldownDetailQuery(mt2, mcId, level, linkInfo2);
return(q);
}
else
{
throw new ArgumentException("Invalid tableName: " + mt.Name);
}
}
/// <summary>
/// Get the name of the SQL statement group that this table should be included in for the purposes of executing searches
/// </summary>
/// <param name="qt"></param>
/// <returns></returns>
public static string GetTableCriteriaGroupName(QueryTable qt)
{
MetaTable mt = null;
IMetaBroker imb = null;
string groupName = null;
try
{
mt = qt.MetaTable;
imb = MetaBrokerUtil.GetGlobalBroker(mt.MetaBrokerType);
groupName = imb.GetTableCriteriaGroupName(qt).ToUpper();
return(groupName);
}
catch (Exception ex)
{
DebugLog.Message(ex);
return("");
}
}
/// <summary>
/// Return true if table allows the use of temp database table key lists
/// </summary>
/// <param name="qt"></param>
/// <returns></returns>
public static bool AllowTempDbTableKeyLists(
QueryTable qt)
{
MetaTable mt = qt.MetaTable;
if (mt == null)
{
return(false);
}
if (mt.MetaBrokerType == MetaBrokerType.Unknown ||
!GlobalBrokerExists(mt.MetaBrokerType))
{
return(false);
}
IMetaBroker mb = GlobalBrokers[(int)mt.MetaBrokerType];
return(mb.AllowTempDbTableKeyLists(qt));
}
/// <summary>
/// Get metabroker object for metatable & initialize
/// </summary>
/// <param name="mt"></param>
/// <returns></returns>
public static IMetaBroker GetGlobalBroker(MetaBrokerType mbType)
{
if (mbType == MetaBrokerType.Unknown)
{
return(null);
}
if (GlobalBrokers == null)
{
return(null);
}
int gbi = (int)mbType;
if (gbi < 0 || gbi > GlobalBrokers.Length)
{
return(null);
}
IMetaBroker mb = GlobalBrokers[gbi];
return(mb);
}
/// <summary>
/// Gets a dictionary by MetaBrokerType of the assay metatables in a query that can be retrieved in an unpivoted form
/// </summary>
/// <param name="q"></param>
Dictionary <MetaBrokerType, List <MetaTable> > GetUnpivotedAssayMetaTables
(Query q)
{
QueryTable qt;
MetaTable mt;
int qti;
Dictionary <MetaBrokerType, List <MetaTable> > dict = new Dictionary <MetaBrokerType, List <MetaTable> >();
for (qti = 0; qti < q.Tables.Count; qti++)
{
qt = Query.Tables[qti];
mt = qt.MetaTable;
MetaBrokerType mbt = mt.MetaBrokerType;
IMetaBroker mb = MetaBrokerUtil.GetGlobalBroker(mbt);
if (mb == null)
{
continue;
}
if (!mb.CanBuildUnpivotedAssayResultsSql)
{
continue;
}
if (!dict.ContainsKey(mbt))
{
dict[mbt] = new List <MetaTable>();
}
dict[mbt].Add(mt);
}
return(dict);
}
