/// <summary>
/// Build the sql for the query
/// </summary>
/// <param name="qt"></param>
/// <returns></returns>
public override string BuildSql(
ExecuteQueryParms eqp)
{
string sql = base.BuildSql(eqp);
return(sql);
}
/// <summary>
/// PrepareQuery
/// </summary>
/// <param name="eqp"></param>
/// <returns></returns>
public override string PrepareQuery(
ExecuteQueryParms eqp)
{
Sql = BuildSql(eqp);
return(Sql);
}
/// <summary>
/// Build the sql for the query
/// </summary>
/// <param name="eqp"></param>
/// <returns></returns>
public override string BuildSql(
ExecuteQueryParms eqp)
{
Eqp = eqp; // save for possible later reference
return("NoSql"); // NonSqlMetaBroker placeholder
}
/// <summary>
/// Execute the query
/// </summary>
/// <param name="eqp"></param>
public override void ExecuteQuery(
ExecuteQueryParms eqp)
{
base.ExecuteQuery(eqp);
ActivityClass = new ActivityClass();
return;
}
/// <summary>
/// Execute query for new set of keys
/// </summary>
/// <param name="eqp"></param>
public override void ExecuteQuery(
ExecuteQueryParms eqp)
{
CidToStructureDict = null;
KeyListPos = -1;
MapPos = -1;
return;
}
/// <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>
/// Execute query for new set of keys
/// </summary>
/// <param name="eqp"></param>
public override void ExecuteQuery(
ExecuteQueryParms eqp)
{
Eqp = eqp; // save for possible later reference
MetaTable mt = eqp?.QueryTable?.MetaTable;
GSP(mt).ExecuteQuery(eqp);
return;
}
/// <summary>
/// Prepare for execution
/// </summary>
/// <param name="eqp"></param>
/// <returns></returns>
public override string PrepareQuery(
ExecuteQueryParms eqp)
{
Eqp = eqp; // save for possible later reference
Qt = eqp.QueryTable;
MetaTable mt = eqp?.QueryTable?.MetaTable;
return(GSP(mt).PrepareQuery(eqp));
}
/// <summary>
/// Build sql and prepare query
/// </summary>
/// <param name="eqp">ExecuteQueryParms</param>
/// <returns>Sql for the query</returns>
public override string PrepareQuery(
ExecuteQueryParms eqp)
{
Eqp = eqp;
Qt = eqp.QueryTable;
Sql = BuildSql(eqp);
OrderBy = "";
return(Sql);
}
/// <summary>
/// Execute query
/// </summary>
/// <param name="eqp"></param>
public override void ExecuteQuery(
ExecuteQueryParms eqp)
{
Eqp = eqp;
Qt = eqp.QueryTable;
MetaTable mt = Qt.MetaTable;
if (string.IsNullOrEmpty(Sql))
{
return; // nothing to do here
}
base.ExecuteQuery(eqp);
}
/// <summary>
/// Build the sql for the query
/// </summary>
/// <param name="eqp"></param>
/// <returns></returns>
public override string BuildSql(
ExecuteQueryParms eqp)
{
string sql = "";
Eqp = eqp;
if (Eqp == null)
{
DebugMx.DataException("Eqp parameter is null");
}
Qt = eqp.QueryTable;
if (Qt == null)
{
DebugMx.DataException("Eqp.QueryTable is null");
}
MetaTable mt = Qt.MetaTable;
if (mt == null)
{
DebugMx.DataException("Metatable not defined for CalcField Query Table");
}
Query q = eqp.Qe.Query;
QueryTableData[] qtd = eqp.Qe.Qtd; // query table data
QueryColumn qc;
MetaColumn mc;
KeyMc = mt.KeyMetaColumn;
if (KeyMc == null)
{
throw new Exception("Key (compound number) column not found for MetaTable " + mt.Name);
}
KeyQci = Qt.GetQueryColumnIndexByName(KeyMc.Name);
KeyQc = Qt.QueryColumns[KeyQci];
KeyQc.Selected = true; // be sure key is selected
Qt.MetaTable.KeyMetaColumn.ColumnMap = ""; // reset key column mapping
Query q2 = InitializeSubQuery(Qt);
foreach (QueryTable qtx in q2.Tables)
{
// todo...
}
return(sql);
}
/// <summary>
/// Generate SQL
/// </summary>
/// <param name="eqp"></param>
/// <returns></returns>
public override string BuildSql(
ExecuteQueryParms eqp)
{
Eqp = eqp;
Qt = eqp.QueryTable;
MetaTable mt = Qt.MetaTable;
KeyMc = mt.KeyMetaColumn;
string sql = base.BuildSql(eqp);
sql = FormatActivityBinExpression(sql);
return(sql);
}
/// <summary>
/// Prepare for execution
/// </summary>
/// <param name="eqp"></param>
/// <returns></returns>
public override string PrepareQuery(
ExecuteQueryParms eqp)
{
Eqp = eqp;
Qt = eqp.QueryTable;
//PrepareForDecompose(Qt, eqp.Qe.Query); // setup again for this broker instance
SelectList = new List<MetaColumn>();
foreach (QueryColumn qc in Qt.QueryColumns)
{
if (qc.Selected || qc.IsKey)
SelectList.Add(qc.MetaColumn);
}
return ""; // No SQL returned
}
/// <summary>
/// Execute Related Structure Search
/// </summary>
/// <param name="eqp"></param>
void ExecuteRelatedStructureSearch(
ExecuteQueryParms eqp)
{
if (RSS == null)
{
RSS = new RelatedStructureSearch();
Query q = StructCriteriaQc.QueryTable.Query;
QueryTable qt = StructCriteriaQc.QueryTable;
MetaTable mt = qt.MetaTable;
QueryColumn qc = StructCriteriaQc;
MetaColumn mc = qc.MetaColumn;
RSS.QueryMtName = mt.Name;
RSS.QueryChimeString = Pssc.Molecule.GetChimeString(); // if no cid in options use passed structure
RSS.IncludeQueryStructure = true;
RSS.SearchCorp = (Lex.Contains(mt.Name, "Corp"));
RSS.SearchChembl = (Lex.Contains(mt.Name, "ChEMBL"));
RSS.SearchFSS = SST.IsFull(Pssc.SearchTypeUnion);
RSS.SearchMmp = SST.IsMmp(Pssc.SearchTypeUnion);
RSS.SearchSmallWorld = SST.IsSw(Pssc.SearchTypeUnion);
RSS.SearchSim = SST.IsSim(Pssc.SearchTypeUnion);
RSS.SearchSSS = SST.IsSSS(Pssc.SearchTypeUnion);
if (RSS.SearchMmp && Lex.IsUndefined(RSS.QueryCid)) // try to get a cid from the structure if we are searching MMP and don't already have one
{
int corpId = Pssc.Molecule.GetCompoundId();
if (corpId > 0)
{
RSS.QueryCid = corpId.ToString();
}
}
RSS.KeysToExclude = q.KeysToExclude;
RSS.ExecuteSearch();
}
BuildRelatedStructureSearchQueryEngineRows(eqp);
return;
}
/// <summary>
/// Return true if associated query is a valid non-Sql search
/// </summary>
/// <param name="eqp"></param>
/// <returns></returns>
public static bool IsNonSqlStructureSearchQueryTable(
ExecuteQueryParms eqp)
{
if (Lex.IsDefined(eqp.CallerSuppliedCriteria))
{
return(false); // return false if retrieval step of query
}
QueryColumn strQc = eqp.QueryTable.FirstStructureQueryColumn;
if (strQc == null || strQc.Criteria == "")
{
return(false);
}
bool result = CanExecuteInternalStructureSearch(strQc);
return(result);
}
/// <summary>
/// Build the set of results rows based on the current key set
/// </summary>
/// <param name="eqp"></param>
void BuildRelatedStructureSearchQueryEngineRows(
ExecuteQueryParms eqp)
{
HashSet <string> keySet = new HashSet <string>();
if (eqp.SearchKeySubset != null) // subsetting
{
foreach (string cid0 in eqp.SearchKeySubset)
{
keySet.Add(cid0);
}
}
List <StructSearchMatch> recs = RSS.AllMatches; // start with full match list
Results = new List <object[]>(); // destination results list (may or may not be subsetted)
for (int ri = 0; ri < recs.Count; ri++)
{
StructSearchMatch m = recs[ri];
if (keySet != null && keySet.Count > 0 && Lex.IsDefined(m.SrcCid))
{
if (!keySet.Contains(m.SrcCid))
{
continue;
}
}
if (RestrictedDatabaseView.KeyIsRetricted(m.SrcCid))
{
continue;
}
object[] voa = new object[2];
voa[0] = m.SrcCid;
voa[1] = m;
Results.Add(voa);
}
CursorPos = -1;
return;
}
/// <summary>
/// Build a single-parameter in-list by converting a key list string into a table using string operations and the Dual table
/// (From Tom Kyte: Varying in lists..., 2-June-2006)
/// (Note that this only works for strings up to 4000 chars and not currently used.)
/// </summary>
/// <param name="eqp"></param>
/// <param name="keyName"></param>
/// <param name="keyList"></param>
/// <param name="firstKeyIdx"></param>
/// <param name="keyCount"></param>
/// <returns></returns>
public string BuildSingleParameterOracleKeyListPredicate(
ExecuteQueryParms eqp,
string keyName,
List <string> keyList,
int firstKeyIdx,
int keyCount,
out string keyListString)
{
string sql =
keyName + @" in (
select
trim( substr (txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1)
- instr (txt, ',', 1, level) -1 ) )
as token
from (select ','||:0||',' txt
from dual)
connect by level <=
length(:0)-length(replace(:0,',','')) + 1
)" ;
StringBuilder sb = new StringBuilder();
for (int i1 = 0; i1 < keyCount; i1++)
{
string sKey = keyList[firstKeyIdx + i1];
if (Lex.IsNullOrEmpty(sKey))
{
continue;
}
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append(sKey);
}
keyListString = sb.ToString();
return(sql);
}
/// <summary>
/// Build the set of results rows based on the current key set
/// </summary>
/// <returns></returns>
void BuildECFP4QueryEngineRows(
ExecuteQueryParms eqp)
{
HashSet <string> keySet = new HashSet <string>();
if (eqp.SearchKeySubset != null) // subsetting
{
foreach (string cid0 in eqp.SearchKeySubset)
{
keySet.Add(cid0);
}
}
List <StructSearchMatch> recs = Ecfp4Dao.MatchList; // start with full match list
Results = new List <object[]>(); // destination results list (may or may not be subsetted)
for (int ri = 0; ri < recs.Count; ri++)
{
StructSearchMatch m = recs[ri];
if (keySet != null && keySet.Count > 0 && Lex.IsDefined(m.SrcCid))
{
if (!keySet.Contains(m.SrcCid))
{
continue;
}
}
object[] voa = new object[VoLength];
voa[0] = m.SrcCid;
if (VoLength > 1)
{
voa[1] = m.MatchScore;
}
Results.Add(voa);
}
CursorPos = -1;
return;
}
/// <summary>
/// Execute SmallWorld search
/// </summary>
/// <param name="eqp"></param>
void ExecuteSmallWorldSearch(
ExecuteQueryParms eqp)
{
if (!SmallWorldDao.IsSmallWorldAvailable())
{
throw new UserQueryException("SmallWorld searching is not currently available.");
}
Query q = eqp.QueryTable.Query;
QueryTable qt = eqp.QueryTable;
SmallWorldPredefinedParameters swp = Pssc.SmallWorldParameters;
string smiles = Pssc.Molecule.GetSmilesString();
swp.Smiles = smiles;
//swp.Database = "Corp";
LastSwNonSqlBroker = null; // debug - force new broker
if (SwDao == null) // if Dao not allocated then allocate & execute the search
{
if (LastSwNonSqlBroker != null && LastSwNonSqlBroker.Pssc != null && LastSwNonSqlBroker.Pssc.SmallWorldParameters != null &&
LastSwNonSqlBroker.Pssc.SmallWorldParameters.Serialize() == swp.Serialize())
{
SwDao = LastSwNonSqlBroker.SwDao; // get rows from previous search
SwDao.BuildSwToQeColumnMap(SelectList); // reset the column map for this broker instance
}
else
{
SwDao = new SmallWorldDao();
SwDao.KeysToExclude = q.KeysToExclude;
SwDao.ExecuteSearch(swp);
SwDao.BuildSwToQeColumnMap(SelectList); //
LastSwNonSqlBroker = this; // remember for future use
}
}
BuildSmallWorldQueryEngineRows(eqp); // build the QE row set (filtered) from the SW rowset
return;
}
/// <summary>
/// Build in list predicate using temporary database table
/// </summary>
/// <param name="eqp"></param>
/// <param name="baseSql"></param>
/// <param name="keyName"></param>
/// <param name="keyList"></param>
/// <param name="firstKeyIdx"></param>
/// <param name="keyCount"></param>
/// <returns></returns>
public string BuildTempDbTableKeyListPredicate(
ExecuteQueryParms eqp,
ref string baseSql,
string keyName,
List <string> keyList,
int firstKeyIdx,
int keyCount)
{
bool intKey = true;
if (QueryEngine.AllowNetezzaUse && eqp.AllowNetezzaUse) // && keyCount > MaxNetzzaInListItemCount)
{
return(DbCommandMx.BuildNetezzaTempDbTableKeyListPredicate(keyName, keyList, firstKeyIdx, keyCount));
}
else // if (keyCount > MaxOracleInListItemCount)
{
return(DbCommandMx.BuildOracleTempDbTableKeyListPredicate(ref baseSql, keyName, intKey, keyList, firstKeyIdx, keyCount));
}
}
/// <summary>
/// Execute CDK ECFP4 search
/// </summary>
/// <param name="eqp"></param>
void ExecuteECFP4Search(
ExecuteQueryParms eqp)
{
CdkSimSearchMx dao = Ecfp4Dao;
if (!CdkSimSearchMx.IsSearchingAvailable())
{
throw new UserQueryException("ECFP4 searching is not currently available.");
}
Query q = eqp.QueryTable.Query;
QueryTable qt = eqp.QueryTable;
MetaTable mt = qt.MetaTable;
if (dao == null) // if Dao not allocated then allocate Dao & execute the search
{
dao = Ecfp4Dao = new CdkSimSearchMx();
dao.KeysToExclude = q.KeysToExclude;
if (eqp.SearchKeySubset != null)
{
dao.SearchKeySubset = new HashSet <string>(eqp.SearchKeySubset);
}
string databases = mt.Name + "," + mt.Label; // quick/dirty DB Id
List <StructSearchMatch> hits = dao.ExecuteSearch(Pssc.Molecule.GetMolfileString(), databases, FingerprintType.Circular, Pssc.MinimumSimilarity, Pssc.MaxSimHits);
VoLength = 1;
if (qt.GetSelectedMolsimQueryColumn() != null)
{
VoLength = 2;
}
}
BuildECFP4QueryEngineRows(eqp); // build the QE row set (filtered)
return;
}
/// <summary>
/// Execute non-Oracle query
/// </summary>
/// <param name="eqp"></param>
public override void ExecuteQuery(
ExecuteQueryParms eqp)
{
QueryTable qt = Eqp.QueryTable;
MetaTable mt = qt.MetaTable;
if (Pssc.SearchType == StructureSearchType.MolSim && Pssc.SimilarityType == SimilaritySearchType.ECFP4)
{
ExecuteECFP4Search(eqp);
}
else if (Pssc.SearchType == StructureSearchType.SmallWorld)
{
ExecuteSmallWorldSearch(eqp);
}
else if (Pssc.SearchType == StructureSearchType.Related)
{
ExecuteRelatedStructureSearch(eqp);
}
else if (mt.MetaBrokerType == MetaBrokerType.Annotation)
{
ExecuteAnnotationStructureSearch();
}
else if (Lex.StartsWith(mt.Name, "USERDATABASE_STRUCTURE_"))
{
ExecuteUserDatabaseStructureSearch();
}
else
{
ExecuteInternalOracleStructureColumnSearch(); // internal search of structures stored in general oracle table column
}
return;
}
/// <summary>
/// Build the set of results rows based on the current key set
/// </summary>
/// <returns></returns>
void BuildSmallWorldQueryEngineRows(
ExecuteQueryParms eqp)
{
HashSet <string> keySet = new HashSet <string>();
if (eqp.SearchKeySubset != null) // subsetting
{
foreach (string cid0 in eqp.SearchKeySubset)
{
keySet.Add(cid0);
}
}
List <SmallWorldMatch> recs = SwDao.MatchList; // start with full match list
Results = new List <object[]>(); // destination results list (may or may not be subsetted)
for (int ri = 0; ri < recs.Count; ri++)
{
SmallWorldMatch m = recs[ri];
if (keySet != null && keySet.Count > 0 && Lex.IsDefined(m.Cid))
{
if (!keySet.Contains(m.Cid))
{
continue;
}
}
object[] voa = m.BuildVoa(SwDao, ri);
Results.Add(voa);
}
CursorPos = -1;
return;
}
/// <summary>
/// Read in data, pivot & buffer for supplied set of rows.
/// This is called for retrieval only, not for search
/// </summary>
/// <param name="eqp"></param>
public override void ExecuteQuery(
ExecuteQueryParms eqp)
{
DbCommandMx drd;
int rowsFetched = 0, vosCreated = 0;
MetaTable mt;
MetaColumn mc = null;
DateTime dt;
PivotMetaBroker mb;
List <GenericMetaBroker> mbList;
string cid, pivotKey, tableFilter, s, txt, tok;
int fci, mci, pvi, pci, si, i1;
object[] vo = null;
object o;
if (!PivotInCode) // let Oracle do the pivoting?
{
base.ExecuteQuery(eqp);
return;
}
// Self-pivot. Read & buffer data for all query tables from same Source/TableFilterColumns for key set if we are the first table for Source
int t0 = TimeOfDay.Milliseconds();
Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = eqp.Qe.MetaBrokerStateInfo;
mt = eqp.QueryTable.MetaTable;
string sourceKey = mt.TableMap + "," + Csv.JoinCsvString(mt.TableFilterColumns); // grouping based on source sql
MultiTablePivotBrokerTypeData mpd = (MultiTablePivotBrokerTypeData)mbsi[sourceKey];
if (mpd.FirstTableName != mt.Name)
{
return; // retrieve data for all tables when we see first table
}
mpd.ClearBuffers();
// Build sql
StringBuilder sb = new StringBuilder(); // build filter to select for desired metatable
tableFilter = "";
if (mt.TableFilterColumns.Count == 1)
{ // build single in list if single filter column
foreach (string mtName in mpd.MbInstances.Keys)
{
mt = MetaTableCollection.Get(mtName);
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append(mt.TableFilterValues[0]);
tableFilter = mt.TableFilterColumns[0] + " in (" + sb.ToString() + ")";
}
}
else // multiple table filter columns, build and/or expressions
{
foreach (string mtName in mpd.MbInstances.Keys)
{
mt = MetaTableCollection.Get(mtName);
if (sb.Length > 0)
{
sb.Append(" or ");
}
tableFilter = "(" + GetTableFilterCriteria(mt) + ")";
sb.Append(tableFilter);
}
tableFilter = "(" + sb.ToString() + ")";
}
string sql = "select * from " + mt.TableMap + " where ";
if (tableFilter != "")
{
sql += tableFilter + " and ";
}
sql += mt.KeyMetaColumn.ColumnMap + " in (<list>) ";
// Read unpivoted data, merge/pivot & buffer pivoted rows
List <string> keySubset = eqp.SearchKeySubset;
if (keySubset == null)
{
keySubset = GetPreviewSubset(); // assume previewing of single table if no subset
}
List <string> parmList = new List <string>();
for (i1 = 0; i1 < keySubset.Count; i1++) // copy keys to parameter array properly normalized
{
string key = CompoundId.NormalizeForDatabase((string)keySubset[i1], Qt.MetaTable);
if (key != null)
{
parmList.Add(key);
}
}
drd = new DbCommandMx();
drd.PrepareListReader(sql, DbType.String);
drd.ExecuteListReader(parmList);
while (drd.Read())
{
rowsFetched++;
string tableFilterKey = ""; // get column values to identify table
for (fci = 0; fci < mt.TableFilterColumns.Count; fci++)
{
o = drd.GetObjectByName(mt.TableFilterColumns[fci]);
if (o == null)
{
s = "";
}
else
{
s = o.ToString();
}
if (tableFilterKey != "")
{
tableFilterKey += ", ";
}
tableFilterKey += s;
}
mt = mpd.TableFilterValuesToMetaTableDict[tableFilterKey];
if (mt == null)
{
continue; // continue if don't know about this table
}
if (!mpd.MbInstances.ContainsKey(mt.Name))
{
continue; // have row hash for broker?
}
int mbIdx = 0;
mb = (PivotMetaBroker)mpd.GetFirstBroker(mt.Name, out mbList);
while (true) // copy out for each metabroker
{
mt = mb.Qt.MetaTable;
if (mt == null)
{
continue;
}
if (mb.MultipivotRowDict == null)
{
mb.MultipivotRowDict = new Dictionary <string, object[]>();
}
string rowKey = "";
for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++)
{
o = drd.GetObjectByName(mt.PivotMergeColumns[mci]);
if (o == null)
{
s = "<null>";
}
else
{
s = o.ToString();
}
rowKey += "<" + s + ">";
}
if (mb.MultipivotRowDict.ContainsKey(rowKey)) // have entry for row?
{
vo = (object[])mb.MultipivotRowDict[rowKey];
}
else // new row, create vo for it & fill in merged column values
{
vo = new Object[mb.SelectList.Count];
for (si = 0; si < mb.SelectList.Count; si++) // transfer non-pivoted values
{
mc = mb.SelectList[si];
if (mc.PivotValues != null)
{
continue; // skip pivoted cols for now
}
for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++)
{
if (Lex.Eq(mc.ColumnMap, mt.PivotMergeColumns[mci]))
{
o = drd.GetObjectByName(mt.PivotMergeColumns[mci]);
if (mc.IsKey) // normalize cid adding prefix as needed
{
o = CompoundId.Normalize(o.ToString(), mt);
}
vo[si] = o;
break;
}
}
}
mb.MultipivotRowDict[rowKey] = vo;
vosCreated++;
}
// Pivot out data based on pivot column values
if (mb.PivotKeys == null)
{ // build set of pivot keys for the pivoted columns in the table if not done yet
mb.PivotKeys = new string[mb.SelectList.Count];
for (si = 0; si < mb.SelectList.Count; si++)
{
mc = mb.SelectList[si];
if (mc.PivotValues == null)
{
continue; // skip non-pivoted cols
}
pivotKey = "";
for (pvi = 0; pvi < mc.PivotValues.Count; pvi++)
{
pivotKey += "<" + mc.PivotValues[pvi].ToLower() + ">";
}
mb.PivotKeys[si] = pivotKey;
}
}
pivotKey = "";
for (pci = 0; pci < mt.PivotColumns.Count; pci++)
{ // build pivot key for this unpivoted row
o = drd.GetObjectByName(mt.PivotColumns[pci]);
if (o == null)
{
s = "<null>";
}
else
{
s = o.ToString().ToLower();
}
pivotKey += "<" + s + ">";
}
for (si = 0; si < mb.SelectList.Count; si++) // transfer pivoted values
{
if (mb.PivotKeys[si] == null || // skip non-pivoted cols
pivotKey != mb.PivotKeys[si])
{
continue; // and non-matches
}
mc = mb.SelectList[si];
int ci = drd.Rdr.GetOrdinal(mc.ColumnMap);
if (mc.DataType == MetaColumnType.Integer)
{
if (!mc.DetailsAvailable) // simple scalar value
{
vo[si] = drd.GetInt(ci);
}
else // value with possible resultId, linking information
{
txt = drd.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.Number)
{
if (!mc.DetailsAvailable) // simple scalar value
{
vo[si] = drd.GetDouble(ci);
}
else // value with possible resultId, linking information
{
txt = Dr.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.QualifiedNo)
{
// todo
}
else if (mc.DataType == MetaColumnType.String)
{
if (!mc.DetailsAvailable)
{
vo[si] = drd.GetString(ci);
}
else // value with possible resultId, linking information
{
txt = Dr.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.Date)
{
if (!mc.DetailsAvailable) // simple scalar value
{
vo[si] = drd.GetDateTime(ci);
}
else // value with possible resultId, linking information
{
txt = Dr.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.Structure)
{ // structures come in as compound identifiers (todo: fix for annotation)
tok = Dr.GetValue(si).ToString();
cid = CompoundId.Normalize(tok, Qt.MetaTable);
vo[si] = cid;
}
else if (mc.DataType == MetaColumnType.MolFormula)
{
vo[si] = drd.GetString(ci);
}
else if (mc.DataType == MetaColumnType.DictionaryId)
{
try // Id may be string or integer value
{ vo[si] = drd.GetString(ci); }
catch (Exception ex)
{ vo[si] = drd.GetInt(ci); }
}
else if (mc.DataType == MetaColumnType.Image)
{
try // Id may be string or integer value
{ vo[si] = drd.GetString(ci); }
catch (Exception ex)
{ vo[si] = drd.GetInt(ci); }
}
else if (mc.DataType == MetaColumnType.Hyperlink)
{
txt = drd.GetString(ci);
Hyperlink hlink = new Hyperlink();
vo[si] = hlink;
hlink.Text = txt;
}
}
if (mbList == null)
{
break; // single broker
}
mbIdx++; // go to next broker
if (mbIdx >= mbList.Count)
{
break; // at end of brokers?
}
mb = (PivotMetaBroker)mbList[mbIdx];
} // end of broker loop
} // end of read loop
drd.Dispose();
return;
}
/// <summary>
/// Build and save SQL for use in Spotfire information link
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public static int SaveSpotfireSql(
string sqlStmtName,
Query query)
{
DbConnectionMx conn = null;
string qtSql = "", sql, expr;
string keys = null;
string keyColName = "";
string t1KeyColExpr = "", keyColExpr;
string rootDataSource = "DEV857";
string rootSchema = "MBS_OWNER";
//if (query.Tables.Count != 1) throw new Exception("Can only save Spotfire Sql for single-table queries");
Query q = query.Clone();
// Clean up query before generating SQL
q.KeyCriteria = q.KeyCriteriaDisplay = "";
List <QueryTable> qtToRemove = new List <QueryTable>();
foreach (QueryTable qt0 in q.Tables)
{
if (qt0.SelectedCount <= 1 ||
!qt0.MetaTable.RetrievesDataFromQueryEngine)
{
qtToRemove.Add(qt0);
continue;
}
foreach (QueryColumn qc0 in qt0.QueryColumns) // clear any criteria
{
if (Lex.IsDefined(qc0.Criteria))
{
qc0.Criteria = qc0.CriteriaDisplay = "";
}
}
}
foreach (QueryTable qt0 in qtToRemove)
{
q.RemoveQueryTable(qt0);
}
string selectList = ""; // top-level list of selected columns
string fromList = ""; // sql for each QueryTable
string joinCriteria = ""; // join criteria between QueryTables
int remapCount = 0;
q.AssignUndefinedAliases();
q.MarkDuplicateNamesAndLabels();
for (int ti = 0; ti < q.Tables.Count; ti++)
{
QueryTable qt = q.Tables[ti];
if (ti == 0)
{
keyColName = qt.MetaTable.KeyMetaColumn.Name;
}
QueryEngine qe = new QueryEngine();
ExecuteQueryParms eqp = new ExecuteQueryParms(qe, qt);
eqp.ReturnQNsInFullDetail = false;
qtSql = qe.BuildSqlForSingleTable(eqp);
qtSql = Lex.Replace(qtSql, "/*+ hint */ ", "");
conn = DbConnectionMx.MapSqlToConnection(ref qtSql, rootDataSource, rootSchema); // convert SQL to use dblinks from root source/schema
if (conn == null)
{
throw new Exception("Connection not found for: " + rootDataSource);
}
// Recast numeric cols that are integers as integers for Spotfire
List <DbColumnMetadata> cmdList = OracleMx.GetColumnMetadataFromSql(qtSql, conn);
string qtSelectList = "";
remapCount = 0; // number of cols remapped
int sci = -1;
foreach (QueryColumn qc in qt.QueryColumns)
{
if (!qc.Selected)
{
continue;
}
sci++; // synch with cmdList
MetaColumn mc = qc.MetaColumn;
string mcName = mc.Name;
if (q.Tables.Count > 1) // if more than one table qualify by table name
{
mcName = qt.Alias + "." + mcName;
}
string colName = qc.UniqueName;
//if (mc.Name == "CORP_SBMSN_ID") mc = mc; // debug
//if (mc.DataType == MetaColumnType.CompoundId) mc = mc; // debug
if (mc.IsNumeric && (mc.DataType == MetaColumnType.Integer || mc.DataType == MetaColumnType.CompoundId))
{
DbColumnMetadata md = cmdList[sci];
expr = "cast (" + mcName + " as integer) " + colName; // integer same as number(22,0)--- " as number(38, 0)) " + expr;
}
else if (mcName != colName)
{
expr = mcName + " " + colName;
remapCount++;
}
else
{
expr = mc.Name;
}
if (qtSelectList != "")
{
qtSelectList += ", ";
}
qtSelectList += expr;
}
if (selectList != "")
{
selectList += ", ";
}
selectList += qtSelectList;
if (fromList != "")
{
fromList += ", ";
}
fromList += "(" + qtSql + ") " + qt.Alias;
keyColExpr = qt.Alias + "." + qt.KeyQueryColumn.MetaColumn.Name;
if (ti == 0)
{
t1KeyColExpr = keyColExpr;
}
else
{
if (joinCriteria != "")
{
joinCriteria += " and ";
}
joinCriteria += keyColExpr + " (+) = " + t1KeyColExpr;
}
}
selectList += " "; // be sure last col name in list is delimited with a space
if (q.Tables.Count == 1 && remapCount == 0)
{
sql = qtSql; // simple single table with no remapping of cols
}
else // combine list of elements
{
sql =
"select " + selectList +
" from " + fromList;
if (joinCriteria != "")
{
sql += " where " + joinCriteria;
}
sql = "select * from (" + sql + ")"; // encapsulate the SQL
}
int v2 = SpotfireDao.InsertSpotfireSql(sqlStmtName, 0, sql, keyColName, null, Security.UserName);
return(v2);
}
/// Build an unparameterized key list SQL predicate
/// </summary>
/// <param name="Qt"></param>
/// <param name="keyName">Key column name qualified by table name/alias</param>
/// <summary>
/// Build an unparameterized key list SQL predicate
/// </summary>
/// <param name="eqp"></param>
/// <param name="baseSql"></param>
/// <param name="keyName">Key column name qualified by table name/alias</param>
/// <param name="keyList"></param>
/// <param name="firstKeyIdx"></param>
/// <param name="keyCount"></param>
/// <param name="keyCriteria">Full criteria including col name operator and list</param>
/// <param name="keyListString">Just the list of keys</param>
public void BuildUnparameterizedKeyListPredicate(
ExecuteQueryParms eqp,
ref string baseSql,
string keyName,
List <string> keyList,
int firstKeyIdx,
int keyCount,
out string keyCriteria,
out string keyListString)
{
StringBuilder sb;
int i1;
QueryTable qt = eqp.QueryTable;
MetaTable mt = qt.MetaTable;
bool integerKey = mt.IsIntegerKey();
List <StringBuilder> sublists = new List <StringBuilder>();
sb = new StringBuilder();
sublists.Add(sb);
int sublistKeyCount = 0; // current keys in predicate
keyCriteria = keyListString = null;
for (i1 = 0; i1 < keyCount; i1++)
{
if (sublistKeyCount >= DbCommandMx.MaxOracleInListItemCount)
{
sb = new StringBuilder();
sublists.Add(sb);
sublistKeyCount = 0;
}
if (sb.Length > 0)
{
sb.Append(",");
}
string key = CompoundId.NormalizeForDatabase((string)keyList[firstKeyIdx + i1], qt.MetaTable);
if (key == null)
{
key = NullValue.NullNumber.ToString(); // if fails supply a "null" numeric value
}
if (!integerKey || !Lex.IsInteger(key)) // quote it if not integer column or value
{
key = Lex.AddSingleQuotes(key); // (note: quoted integers can cause mismatches for some database systems, e.g. Denodo)
}
sb.Append(key);
sublistKeyCount++;
}
sb = new StringBuilder();
if (sublists.Count >= 2)
{
sb.Append("("); // wrap in parens if multiple sublists
}
for (int sli = 0; sli < sublists.Count; sli++)
{
if (sli > 0)
{
sb.Append(" or ");
}
sb.Append(keyName + " in (" + sublists[sli] + ")");
}
if (sublists.Count >= 2)
{
sb.Append(")");
}
keyCriteria = sb.ToString();
keyListString = sublists[0].ToString(); // return just the first sublist (adjust later for larger lists)
return;
}
/// <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>
/// Clone ExecuteQueryParms
/// </summary>
/// <returns></returns>
public ExecuteQueryParms Clone()
{
ExecuteQueryParms eqp = (ExecuteQueryParms)this.MemberwiseClone();
return(eqp);
}
/// <summary>
/// Build the sql for a query doing preparation for separate fetch of data.
/// </summary>
/// <param name="parms"></param>
public override string PrepareQuery(
ExecuteQueryParms eqp)
{
MultiTablePivotBrokerTypeData mpd; // multipivot data for this broker type
List <GenericMetaBroker> mbList;
string tableFilterKey;
Eqp = eqp;
Qt = eqp.QueryTable;
MetaTable mt = Qt.MetaTable;
if (!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
{
return(base.PrepareQuery(eqp));
}
PivotedColCount = 0;
foreach (QueryColumn qc1 in Qt.QueryColumns)
{ // if any criteria then pivot individually rather than via multipivot
if (qc1.Criteria != "" && !qc1.IsKey)
{
return(base.PrepareQuery(eqp));
}
if (qc1.MetaColumn.PivotValues != null)
{
PivotedColCount++;
}
}
if (PivotedColCount == 0)
{
return(base.PrepareQuery(eqp)); // must have at least one column to pivot
}
// Store pivot info for queryTable
PivotInCode = true;
BuildSql(eqp); // setup SelectList (don't really need sql)
MpGroupKey = mt.TableMap + "," + Csv.JoinCsvString(mt.TableFilterColumns); // grouping based on source sql
if (!QueryEngine.AllowMultiTablePivot)
{
MpGroupKey += "_" + Qt.MetaTable.Name;
}
if (eqp.Qe.MetaBrokerStateInfo == null)
{
eqp.Qe.MetaBrokerStateInfo = new Dictionary <string, MultiTablePivotBrokerTypeData>();
}
Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = eqp.Qe.MetaBrokerStateInfo;
if (!mbsi.ContainsKey(MpGroupKey))
{ // create entry for data source
mpd = new MultiTablePivotBrokerTypeData();
mbsi[MpGroupKey] = mpd;
mpd.FirstTableName = mt.Name;
mpd.TableFilterValuesToMetaTableDict = new Dictionary <string, MetaTable>();
mpd.TableCodeCsvList = new StringBuilder();
mpd.TableCodeDict = new Dictionary <string, MpdResultTypeData>();
mpd.MbInstances = new Dictionary <string, object>();
}
else
{
mpd = (MultiTablePivotBrokerTypeData)mbsi[MpGroupKey]; // get existing entry
}
string pms = Csv.JoinCsvString(mt.TableFilterValues); // values to identify this metatable
mpd.AddMetaBroker(Qt.MetaTable.Name, this);
// Store TableFilter values associated with metatable
if (mt.TableFilterValues.Count == 1)
{ // handle case with list of values allowed for single filter column (e.g. pivot values)
List <string> sl = Csv.SplitCsvString(mt.TableFilterValues[0]);
foreach (string s in sl)
{
mpd.TableFilterValuesToMetaTableDict[s.ToLower()] = mt;
}
}
else
{
tableFilterKey = "";
foreach (string s in mt.TableFilterValues)
{
if (tableFilterKey != "")
{
tableFilterKey += ", ";
}
tableFilterKey += s;
}
mpd.TableFilterValuesToMetaTableDict[tableFilterKey.ToLower()] = mt;
}
#if false // todo: store codes for quick row identification
if (!mpd.CodeHash.ContainsKey(Qt.MetaTable.Code)) // include assay code
{
mpd.CodeHash[pms] = null; // add key to hash list
if (mpd.Codes.Length > 0)
{
mpd.Codes.Append(",");
}
mpd.Codes.Append(pms);
}
#endif
return(null); // no sql generated here
}
/// <summary>
/// Generate the sql for retrieval from a querytable
/// If this query returns qualified numbers (i.e. is not search part of query or part of calc field)
/// then don't retrieve any data other than the compound id & group id.
/// </summary>
/// <param name="qt"></param>
public override string BuildSql(
ExecuteQueryParms eqp)
{
List <MetaColumn> mcl;
List <QueryColumn> qcl;
QueryColumn qc;
MetaTable mt;
MetaColumn mc;
Hashtable resultKeys;
string tableAlias, txt, tok;
string pivotExprs, pivotTables = "", pivotCriteria;
int ci, qci, i1;
Eqp = eqp;
Qt = eqp.QueryTable;
Exprs = FromClause = OrderBy = ""; // outer sql elements
qcl = Qt.QueryColumns;
mt = Qt.MetaTable;
mcl = mt.MetaColumns;
KeyMc = mt.KeyMetaColumn;
resultKeys = new Hashtable();
SelectList = new List <MetaColumn>(); // list of selected metacolumns
int selectCnt = 0; // counting key
int criteriaCnt = 0; // not counting key
QueryColumn firstCriteriaPivotedCol = null; // first pivoted column with criteria
QueryColumn firstSelectedPivotedCol = null; // first pivoted column that's selected
StringBuilder pivotValueList = new StringBuilder(); // build list of selected result codes
for (qci = 0; qci < qcl.Count; qci++)
{
qc = qcl[qci];
mc = qc.MetaColumn;
if (mc.PivotValues != null && mc.PivotValues.Count == 1 && (qc.Is_Selected_or_Criteria_or_GroupBy_or_Sorted))
{
if (pivotValueList.Length > 0)
{
pivotValueList.Append(",");
}
pivotValueList.Append(mc.PivotValues[0]);
}
if (qc.Is_Selected_or_GroupBy_or_Sorted)
{
selectCnt++;
if (mc.PivotValues != null && firstSelectedPivotedCol == null &&
!eqp.ReturnQNsInFullDetail) // don't count if getting qualified numbers since handled differently
{
firstSelectedPivotedCol = qc;
}
}
if (qc.Criteria != "")
{
criteriaCnt++;
if (mc.PivotValues != null && firstCriteriaPivotedCol == null)
{
firstCriteriaPivotedCol = qc;
}
}
}
if ((eqp.Qe.MqlLogicType != QueryLogicType.And && criteriaCnt > 1))
{
firstSelectedPivotedCol = firstCriteriaPivotedCol = null; // don't use this optimization unless "and" logic
}
// Add key column to selected list if not already selected
qc = Qt.KeyQueryColumn;
if (qc == null)
{
throw new Exception("Key column not found for MetaTable " + mt.Name);
}
if (!qc.Selected) // put key at start of list if not already selected
{
SelectList.Add(mt.KeyMetaColumn);
selectCnt++;
}
////////////////////////////////////////////////////////////////////////////////
// Build sql
////////////////////////////////////////////////////////////////////////////////
//
// An inner query in the from clause pivots out the columns that are
// selected or have criteria
//
////////////////////////////////////////////////////////////////////////////////
pivotExprs = // key field with native col name mapped to mc name
"t0." + mt.KeyMetaColumn.ColumnMap + " " + mt.KeyMetaColumn.Name; // key field with native col name mapped to mc name
pivotCriteria = "";
int pivotCount = 0;
QueryColumn t0Col = null; // column assigned to t0
if (firstCriteriaPivotedCol != null)
{
t0Col = firstCriteriaPivotedCol; // if there is a pivoted column with criteria assign to t0
}
else if (selectCnt == 2 && firstSelectedPivotedCol != null)
{
t0Col = firstSelectedPivotedCol; // single selected pivoted col assigned to t0
}
else
{ // multiple (or zero) selected pivoted cols, build "select unique" to join all pivoted values to
pivotTables = "(select unique ";
for (int mci = 0; mci < mt.PivotMergeColumns.Count; mci++)
{
if (mci > 0)
{
pivotTables += ", ";
}
pivotTables += mt.PivotMergeColumns[mci];
}
pivotTables += " from " + mt.TableMap + " ";
string tableSelectionExpr = GetTableFilterCriteria(mt);
if (pivotValueList.Length > 0) // if we have any pivot value then limit to those
{
if (tableSelectionExpr != "")
{
tableSelectionExpr += " and ";
}
tableSelectionExpr += mt.PivotColumns[0] + " in (" + pivotValueList + ")";
}
pivotTables += " where " + tableSelectionExpr + ") t0 ";
}
// Process query column objects
for (qci = 0; qci < qcl.Count; qci++)
{
qc = qcl[qci];
mc = qc.MetaColumn;
// Pivot out the result
if (!qc.Is_Selected_or_Criteria_or_GroupBy_or_Sorted)
{
continue;
}
if (mt.KeyMetaColumn == mc)
{
; // already have key column
}
else if (mc.PivotValues != null) // pivot out of results table
{
if (qc == t0Col)
{
tableAlias = "t0"; // col to be used as t0
}
else
{
pivotCount++;
tableAlias = "t" + (pivotCount).ToString();
}
pivotExprs += ", "; // add one or more expressions for this column
string colName = tableAlias + "." + mc.ColumnMap; // name for this col qualified by table alias
if (Eqp.ReturnQNsInFullDetail && // return in qn format?
(mc.DataType == MetaColumnType.QualifiedNo || mc.DetailsAvailable))
{
if (qc.Is_Selected_or_GroupBy_or_Sorted)
{ // build expression to retrieve catenated col value & linking info
string colExpr = colName;
if (mc.DataType == MetaColumnType.Date) // return normalized, sortable date string (annotation tables)
{
colExpr = "to_char(" + colName +
",'YYYYMMDD HH24MISS')";
}
string qnExpr =
AppendQnElement(mt.QnQualifier) + " chr(11) || " +
AppendQnElement(mt.QnNumberValue) + " chr(11) || " +
AppendQnElement(mt.QnTextValue) + " chr(11) || " +
AppendQnElement(mt.QnNValue) + " chr(11) || " +
AppendQnElement(mt.QnNValueTested) + " chr(11) || " +
AppendQnElement(mt.QnStandardDeviation) + " chr(11) || " +
AppendQnElement(mt.QnStandardError) + " chr(11) || " +
AppendQnElement(mt.QnLinkValue);
qnExpr = qnExpr.Replace("<TA>", tableAlias);
pivotExprs += qnExpr + " " + mc.Name + ", ";
}
pivotExprs += // native column value for comparison
colName + " " + mc.Name + "_val ";
}
else // not a qualified number or not returning qualified numbers
{
//if (qc.Criteria.ToLower().EndsWith(" is not null")) // if is-not-null criteria then any value will do not just number
//pivotExprs += tableAlias + ".assay_rslt_typ_id " + mc.Name + "_val "; // todo: fix to work with complex criteria
//else
pivotExprs += // just return native value
colName + " " + mc.Name + ", ";
pivotExprs += // native column value for comparison
colName + " " + mc.Name + "_val ";
}
if (pivotTables != "")
{
pivotTables += ", ";
}
pivotTables += GetSourceWithTableFilterCriteria(mt) + " " + tableAlias + " ";
txt = "";
for (int pci = 0; pci < mt.PivotColumns.Count; pci++)
{ // filter unpivoted source for just this column
if (txt != "")
{
txt += " and ";
}
txt += "<TA>." + mt.PivotColumns[pci] + " (+) = " + mc.PivotValues[pci] + " ";
}
if (qc != t0Col) // joint to t0 on merge columns unless we are t0
{
foreach (string mergeCol in mt.PivotMergeColumns)
{
txt += "and <TA>." + mergeCol + " (+) = t0." + mergeCol + " ";
}
}
txt = txt.Replace("<TA>", tableAlias); // plug in proper table alias
if (qc.Criteria != "" || selectCnt <= 2)
{
txt = txt.Replace("(+)", ""); // remove outer join if criteria on this column
}
if (pivotCriteria != "")
{
pivotCriteria += " and ";
}
pivotCriteria += txt + " ";
}
else // nulls for other non-result fields
{
pivotExprs += ", null " + mc.Name + " ";
}
// Add to select list if selected
if (qc.Is_Selected_or_GroupBy_or_Sorted) // if selected add to expression list
{
if (mt.KeyMetaColumn == mc) // save index of compound id value object
{
KeyVoi = SelectList.Count;
}
if (Exprs.Length > 0)
{
Exprs += ",";
}
Exprs += mc.Name;
SelectList.Add(mc);
}
} // end of column loop
FromClause = "(select " + pivotExprs + " " +
"from " + pivotTables;
if (pivotCriteria != "")
{
FromClause += " where " + pivotCriteria;
}
FromClause += ")";
Sql = "select /*+ first_rows */ " + Exprs +
" from " + FromClause;
if (Qt.Alias != "")
{
Sql += " " + Qt.Alias;
}
if (eqp.CallerSuppliedCriteria != "")
{
Sql += " where " + eqp.CallerSuppliedCriteria;
}
return(Sql);
}
