/// <summary>
/// SubstituteBackgroundExportParameters
/// </summary>
/// <param name="sourceHtml"></param>
/// <param name="queryLabel"></param>
/// <param name="fileName"></param>
/// <param name="rowCount"></param>
/// <param name="compoundCount"></param>
/// <param name="viewCmd"></param>
/// <returns></returns>
public static string SubstituteBackgroundExportParameters(
string sourceHtml,
string queryLabel,
string fileName,
int rowCount,
int compoundCount,
bool copiedToDestinationFile,
string viewCmd)
{
string html = sourceHtml.Replace("query-name", queryLabel);
html = html.Replace("file-name", fileName);
html = html.Replace("row-count", rowCount.ToString());
html = html.Replace("compound-count", compoundCount.ToString());
if (copiedToDestinationFile)
{
html = Lex.Replace(html, "prepared for download", "downloaded");
}
string viewLink = "Mobius:Command=" + Lex.AddSingleQuotes(viewCmd); // Uses Mobius pluggable protocol
html = html.Replace(@"file:///\\retrieve-background-export-link", viewLink);
html = html.Replace("retrieve-background-export-command", viewCmd);
return(html);
}
/// <summary>
/// Build query to get summarization detail for RgroupMatrix data element
/// </summary>
/// <param name="mt"></param>
/// <param name="mc"></param>
/// <param name="resultId">act_code.compound_id</param>
/// <returns></returns>
public override Query GetDrilldownDetailQuery(
MetaTable mt,
MetaColumn mc,
int level,
string resultId)
{
QueryColumn qc;
// ResultId is of the form: queryId, mtName, mcName, sn1, sn2,...snn
string[] sa = resultId.Split(',');
int objectId = Int32.Parse(sa[0]);
UserObject uo = UserObjectDao.Read(objectId);
if (uo == null) return null; // no longer there
Query q = Query.Deserialize(uo.Content);
q.ResultKeys = null; // clear any set of result keys
if (q.LogicType == QueryLogicType.Complex)
{ // if complex logic then go simple (todo: fix to handle complex logic)
q.ClearAllQueryColumnCriteria();
q.LogicType = QueryLogicType.And;
}
q.KeyCriteria = "in (";
for (int i1 = 3; i1 < sa.Length; i1++)
{
q.KeyCriteria += Lex.AddSingleQuotes(sa[i1]);
if (i1 < sa.Length - 1) q.KeyCriteria += ",";
}
q.KeyCriteria += ")";
return q;
}
/// <summary>
/// Update the secondary criteria to match the slider
/// </summary>
void UpdateSecondaryCriteria()
{
QueryColumn qc = ColInfo.Qc;
if (ItemFilter.Value == 0)
{
ValueLabel.Text = "(All)";
qc.SecondaryCriteria = qc.SecondaryCriteriaDisplay = "";
}
else if (ItemFilter.Value == Stats.DistinctValueList.Count + 1)
{
ValueLabel.Text = "(Blanks)";
qc.SecondaryCriteria = qc.MetaColumn.Name + " is null";
qc.SecondaryCriteriaDisplay = qc.ActiveLabel + " is null";
}
else
{
MobiusDataType mdt = Stats.DistinctValueList[ItemFilter.Value - 1];
ValueLabel.Text = mdt.FormattedText;
string normalizedString = mdt.FormatForCriteria();
qc.SecondaryCriteria = qc.MetaColumn.Name + " = " + Lex.AddSingleQuotes(normalizedString);
qc.SecondaryCriteriaDisplay = qc.ActiveLabel + " = " + Lex.AddSingleQuotes(ValueLabel.Text);
}
qc.SecondaryFilterType = FilterType.ItemSlider;
FilterBasicCriteriaControl.SyncBaseQuerySecondaryCriteria(qc); // sync any base query
}
/// <summary>
/// Set QueryTable criteria values from TargetSummaryOptions
/// </summary>
/// <param name="TargetSumTable"></param>
public bool SetInQueryTable(
QueryTable targetSumTable)
{
QueryTable qt = targetSumTable;
if (qt == null)
{
return(false);
}
Query q = qt.Query;
QueryColumn qc = qt.GetQueryColumnByNameWithException(MultiDbAssayDataNames.MultiDbViewOptions);
if (qc == null)
{
return(false);
}
string tsoString = Serialize();
qc.Criteria = qc.MetaColumn.Name + " = " + Lex.AddSingleQuotes(tsoString);
qc.CriteriaDisplay = "Edit...";
qt.KeyQueryColumn.Criteria = CidCriteria; // keep in variable criteria form
SetListCriteriaForCol(qt, MultiDbAssayDataNames.GeneSymbol, TargetList);
SetListCriteriaForCol(qt, MultiDbAssayDataNames.GeneFamily, GeneFamilies);
return(true);
}
private void Timer_Tick(object sender, EventArgs e)
{
if (!StructureChanged)
{
return;
}
QueryColumn qc = ColInfo.Qc;
if (String.IsNullOrEmpty(StructureRenditor.MolfileString))
{
qc.SecondaryCriteria = qc.SecondaryCriteriaDisplay = "";
}
else
{
// Chime: CYAAFQwAncwQGj8h7GZ^yjgsajoFd0PQ1OYrnIdaPTl0lGnQYLHH2prJeJi$BhUHcMsE1TyQisJflsW2r293v92iC1^wVm$8wwLM7^krIFa8A1X6Jvu8VIYgCgJ8$y1RuqgCc5ifKbMAflB
string chimeString = MoleculeMx.MolfileStringToChimeString(StructureRenditor.MolfileString);
qc.SecondaryCriteria = "SSS ( " + qc.MetaColumn.Name + ", " + Lex.AddSingleQuotes(chimeString) + ") = 1";
qc.SecondaryCriteriaDisplay = qc.ActiveLabel + " contains substructure";
}
qc.SecondaryFilterType = FilterType.StructureSearch;
QueryManager.QueryResultsControl.UpdateFiltering(ColInfo);
FilterBasicCriteriaControl.SyncBaseQuerySecondaryCriteria(qc); // sync any base query
StructureChanged = false;
return;
}
/// <summary>
/// General table / gene symbol query builder
/// </summary>
/// <param name="tableName"></param>
/// <param name="geneSymbol"></param>
/// <returns></returns>
public static Query BuildTargetAssayQuery(
string tableName,
string geneSymbol)
{
Query q = new Query();
MetaTable mt = MetaTableCollection.GetWithException(tableName);
QueryTable qt = new QueryTable(q, mt);
QueryColumn qc = qt.GetQueryColumnByNameWithException(MultiDbAssayDataNames.GeneSymbol);
qc.Criteria = MultiDbAssayDataNames.GeneSymbol + " = " + Lex.AddSingleQuotes(geneSymbol);
qc.CriteriaDisplay = "= " + geneSymbol;
if (qc.IsKey) // if key column store for that as well
{
q.KeyCriteria = " = " + Lex.AddSingleQuotes(geneSymbol);
q.KeyCriteriaDisplay = "= " + geneSymbol;
}
//qc = qt.GetQueryColumnByNameWithException("top_lvl_rslt");
//qc.Criteria = "top_lvl_rslt = 'Y'"; // only top level results
//qc.CriteriaDisplay = "= Y";
return(q);
}
/// <summary>
/// Convert a simple value list to criteria for a column
/// </summary>
/// <param name="colName"></param>
/// <param name="list"></param>
public static QueryColumn SetListCriteriaForCol(
QueryTable qt,
string colName,
string list)
{
string tok;
QueryColumn qc = qt.GetQueryColumnByName(colName);
if (qc == null)
{
return(null);
}
if (Lex.Eq(list, "All"))
{
list = ""; // treat all as no criteria
}
List <string> values = Csv.SplitCsvString(list);
string c = "", cd = "";
foreach (string s in values)
{
tok = Lex.RemoveAllQuotes(s);
if (Lex.IsNullOrEmpty(tok))
{
continue;
}
if (cd != "")
{
cd += ", ";
}
cd += tok;
if (c != "")
{
c += ", ";
}
if (!qc.MetaColumn.IsNumeric) // quote it if not numeric
{
tok = Lex.AddSingleQuotes(tok);
}
c += tok;
}
if (!Lex.IsNullOrEmpty(c))
{
c = qc.MetaColumn.Name + " IN (" + c + ")";
}
qc.Criteria = c;
qc.CriteriaDisplay = cd;
return(qc);
}
/// <summary>
/// Invoke the editor
/// </summary>
/// <param name="qc"></param>
/// <returns></returns>
public static bool Edit(
QueryColumn qc)
{
if (Lex.StartsWith(qc.MetaColumn.Dictionary, "ContentsTree"))
{
return(CriteriaContentsTreeList.Edit(qc));
}
if (Instance == null)
{
Instance = new CriteriaDictMultSelect();
}
new JupyterGuiConverter().ConvertFormOrUserControl(Instance);
string title = "Search criteria for " + qc.ActiveLabel;
string prompt = "Select one or more " + qc.ActiveLabel + " from the list below.";
string dictName = GetCriteriaSpecificDictionary(qc);
string selections = CheckedListBoxDialog(title, prompt, dictName, qc.Criteria);
if (selections == null)
{
return(false);
}
else
{
if (selections == "")
{
qc.Criteria = qc.CriteriaDisplay = "";
}
else
{
qc.CriteriaDisplay = selections;
if (!qc.MetaColumn.IsNumeric)
{ // quote items if necessary
List <string> items = Csv.SplitCsvString(selections);
for (int i1 = 0; i1 < items.Count; i1++)
{
items[i1] = Lex.AddSingleQuotes(items[i1]);
}
selections = Csv.JoinCsvString(items);
}
qc.Criteria = qc.MetaColumn.Name + " in (" + selections + ")";
}
return(true);
}
}
private void InSavedList_Click(object sender, EventArgs e)
{
Criteria.Focus();
UserObject uo = CidListCommand.SelectListDialog("In Saved List");
if (uo == null)
{
return;
}
string listName = uo.InternalName;
string criteria = "In List " + Lex.AddSingleQuotes(listName);
Instance.InsertCriteria(criteria);
}
static void AddInsertColumn(
string colName,
ref string names,
ref string values,
object obj)
{
if (names != "")
{
names += ",";
}
names += colName;
if (values != "")
{
values += ", ";
}
if (obj == null)
{
values += "null";
}
else if (obj is int && ((int)obj) == NullValue.NullNumber || // store null number values as nulls
obj is double && ((double)obj) == NullValue.NullNumber)
{
values += "null";
}
else if (obj is DateTime)
{
DateTime dt = (DateTime)obj;
if (dt.Equals(DateTime.MinValue))
{
values += "null";
}
else
{
string yyyymmdd = DateTimeMx.Normalize(dt);
values += "to_date('" + yyyymmdd + "','YYYYMMDD')";
}
}
else
{
values += Lex.AddSingleQuotes(obj.ToString());
}
return;
}
/// <summary>
/// Update the secondary criteria to match the slider
/// </summary>
void UpdateSecondaryCriteria()
{
string lowCriteriaDisplayText, lowCriteriaText, highCriteriaDisplayText, highCriteriaText;
QueryColumn qc = ColInfo.Qc;
UpdateLabels(out lowCriteriaDisplayText, out lowCriteriaText, out highCriteriaDisplayText, out highCriteriaText);
qc.SecondaryCriteria = qc.MetaColumn.Name + " between " + Lex.AddSingleQuotes(lowCriteriaText) +
" and " + Lex.AddSingleQuotes(highCriteriaText);
qc.SecondaryCriteriaDisplay = qc.ActiveLabel + " between " + Lex.AddSingleQuotes(lowCriteriaDisplayText) +
" and " + Lex.AddSingleQuotes(highCriteriaDisplayText);
qc.SecondaryFilterType = FilterType.RangeSlider;
FilterBasicCriteriaControl.SyncBaseQuerySecondaryCriteria(qc); // sync any base query
}
/// <summary>
/// Convert list to comma separated form
/// </summary>
/// <param name="removeLeadingZerosFromCids"></param>
/// <param name="quoteItems"></param>
/// <returns></returns>
public string ToListString(
bool removeLeadingZerosFromCids,
bool quoteItems)
{
string cid;
int cncnt = 0, intCid;
StringBuilder sb = new StringBuilder();
foreach (CidListElement e in List)
{
if (e.Cid != "")
{
if (sb.Length > 0)
{
sb.Append(",");
}
cid = e.Cid;
if (removeLeadingZerosFromCids && int.TryParse(cid, out intCid))
{
cid = intCid.ToString();
}
else
{
cid = CompoundId.Normalize(cid);
}
if (quoteItems)
{
cid = Lex.AddSingleQuotes(cid);
}
sb.Append(cid);
cncnt++;
}
}
return(sb.ToString());
}
/// <summary>
/// Convert complex criteria to labeled form suitable for editing in complex criteria editor
/// </summary>
/// <param name="q"></param>
/// <param name="structures">Dictionary of structure names & connection tables</param>
public static LabeledCriteria ConvertComplexCriteriaToEditable(
Query q,
bool includeEditButtons)
{
bool insertBreaks = false;
if (q.ComplexCriteria.IndexOf("\n") < 0)
{
insertBreaks = true;
}
Dictionary <string, string> tAliasMap = GetAliasMap(q);
if (tAliasMap != null && !includeEditButtons)
{ // fixup aliases properly first using editable criteria
ConvertComplexCriteriaToEditable(q, true);
tAliasMap = null;
}
Lex lex = new Lex();
lex.SetDelimiters(" , ; ( ) < = > <= >= <> != !> !<");
string criteria = q.ComplexCriteria;
lex.OpenString(criteria);
StringBuilder sb = new StringBuilder();
PositionedToken lastTok = null;
List <PositionedToken> tokens = new List <PositionedToken>(); // list of tokens seen
LabeledCriteria lc = new LabeledCriteria();
lc.Structures = new Dictionary <string, string>();
while (true)
{
PositionedToken tok = lex.GetPositionedToken();
if (tok == null)
{
break;
}
tokens.Add(tok);
if (lastTok != null)
{ // include same white space between tokens
int wsBeg = lastTok.Position + lastTok.Text.Length;
sb.Append(criteria.Substring(wsBeg, tok.Position - wsBeg));
}
QueryColumn qc = MqlUtil.GetQueryColumn(tok.Text, q); // see if token is column ref
if (qc != null)
{ //query column, map to labeled columns
string label = GetUniqueColumnLabel(qc);
QueryTable qt = qc.QueryTable;
string tName, cName;
MqlUtil.ParseColumnIdentifier(tok.Text, out tName, out cName);
if (tName != null && tName != "") // any table name supplied?
{
if (tAliasMap != null && tAliasMap.ContainsKey(tName.ToUpper()))
{
tName = tAliasMap[tName.ToUpper()];
}
label = tName + "." + label;
}
sb.Append(Lex.Dq(label));
}
else
{ // not a query column reference
string tokText = tok.Text;
string txt = Lex.RemoveSingleQuotes(tokText).ToUpper();
if (UserObject.IsCompoundIdListName(txt))
{
string listName = null;
int objectId = int.Parse(txt.Substring(7));
UserObject uo = UserObjectDao.ReadHeader(objectId);
if (uo != null)
{
listName = uo.InternalName;
}
else
{
listName = "Unknown";
}
tokText = Lex.AddSingleQuotes(listName);
}
if (tokens.Count >= 5)
{ // see if this is a chime string
string sFuncCand = tokens[tokens.Count - 5].Text.ToLower();
if ((Lex.Eq(sFuncCand, "SSS") || Lex.Eq(sFuncCand, "FSS") || Lex.Eq(sFuncCand, "MolSim")) &&
tokText.StartsWith("'") && tokText.EndsWith("'")) // single-quoted chime?
{
string sAlias = "S" + (lc.Structures.Count + 1).ToString();
lc.Structures[sAlias] = Lex.RemoveSingleQuotes(tokText); // save structure in dictionary
if (includeEditButtons)
{
tokText = "[Edit Structure " + sAlias + "]"; // use alias in labeled query
}
else
{
tokText = Lex.AddSingleQuotes(sAlias);
}
}
}
if ((Lex.Eq(tokText, "And") || Lex.Eq(tokText, "Or")) &&
tokens.Count >= 3 && !Lex.Eq(tokens[tokens.Count - 3].Text, "Between") &&
insertBreaks)
{
sb.Append("\n"); // start new line for each and/or
}
sb.Append(tokText); // not query column identifier
}
lastTok = tok;
}
sb.Append(" "); // include final space so additional text is black, also to get correct font
lc.Text = sb.ToString();
// If a table alias changes then update aliases & complex criteria but only if going
// to editable text since ConvertEditableCriteriaToComplex fails otherwise.
if (tAliasMap != null)
{
for (int qti = 0; qti < q.Tables.Count; qti++)
{ // set new table aliases
QueryTable qt = q.Tables[qti];
string alias = "T" + (qti + 1).ToString();
qt.Alias = alias;
}
ConvertEditableCriteriaToComplex(lc, q, null); // update q.ComplexCriteria also
}
return(lc);
}
/// <summary>
/// Build click function and field value text string for output
/// ClickFunction arguments may be defined in the clickfunction definition including col values
/// indicated by fieldName.Value in the metacolumn clickfunction definition.
/// If no args are defined in the clickfunction definition then a field value
/// argument will be added by default, the keyValue if [keyvalue] or
/// [rowcol] as a grid row and column to be returned if these appear in the
/// ClickFunction definition.
/// </summary>
/// <param name="rf">Results field to display link for</param>
/// <param name="vo">Vo contain tuple values</param>
/// <param name="displayValue">Formatted display value for field</param>
/// <returns></returns>
public static string BuildClickFunctionText(
ResultsField rf,
DataRowMx dr,
int dri,
string displayValue)
{
ResultsTable rt;
MetaColumn mc, mc2;
MetaTable mt;
string arg, arg2, argsString;
int ai, rfi, voi;
if (rf == null || dr == null || dri < 0)
{
return("");
}
rt = rf.ResultsTable;
mc = rf.MetaColumn;
mt = mc.MetaTable;
object[] vo = dr.ItemArray;
if (String.IsNullOrEmpty(mc.ClickFunction))
{
return("");
}
List <string> args = Lex.ParseAllExcludingDelimiters(mc.ClickFunction, "( , )", false);
string funcName = args[0]; // click function name
int fieldRefs = 0;
for (ai = 1; ai < args.Count; ai++)
{ // see how many mcName.Value references there are
arg = args[ai];
string suffix = ".Value";
if (!Lex.EndsWith(arg, suffix))
{
continue;
}
arg = arg.Substring(0, arg.Length - suffix.Length);
if (mt.GetMetaColumnByName(arg) != null)
{
fieldRefs++;
}
}
if (fieldRefs == 0) // if no field references add either the field value, key value or grid row and column
{
if (Lex.Eq(funcName, "RunHtmlQuery") || Lex.Eq(funcName, "RunGridQuery") ||
Lex.Eq(funcName, "DisplayWebPage")) // fixups for old functions
{
args.Add(Lex.AddSingleQuotes(mt.Name)); // add metatable name
args.Add(Lex.AddSingleQuotes(mc.Name)); // add metacolumn name
}
if (Lex.Contains(mc.ClickFunction, "[TableColumnValue]")) // convert to metatable, metacolumns, &value refDataTable row & col
{
args.RemoveAt(1); // remove [TableColumnValue] arg
args.Add(Lex.AddSingleQuotes(mt.Name));
args.Add(Lex.AddSingleQuotes(mc.Name));
args.Add(mc.Name + ".Value");
}
else if (Lex.Contains(mc.ClickFunction, "[keyvalue]"))
{
args.Add(mt.KeyMetaColumn.Name + ".value"); // pass key value rather than this col value
}
else
{
args.Add(mc.Name + ".value"); // pass column value
}
}
argsString = "";
for (ai = 1; ai < args.Count; ai++)
{
arg = args[ai];
string suffix = ".Value";
if (!arg.EndsWith(suffix, StringComparison.OrdinalIgnoreCase))
{
continue;
}
arg2 = arg.Substring(0, arg.Length - suffix.Length);
mc2 = mt.GetMetaColumnByName(arg2);
if (mc2 == null)
{
continue; // assume unquoted string constant & pass as is
}
else if (mc2.IsKey && rt.Fields[0].MetaColumn.Name != mc2.Name)
{ // see if undisplayed key value
voi = rt.Fields[0].VoPosition - 1; // position in vo
}
else // find the field name in the list of report fields
{
for (rfi = 0; rfi < rt.Fields.Count; rfi++)
{
if (Lex.Eq(mc2.Name, rt.Fields[rfi].MetaColumn.Name))
{
break;
}
}
if (rfi >= rt.Fields.Count)
{
throw new Exception("Column name not selected in query: " + mc2.Name);
}
voi = rt.Fields[rfi].VoPosition;
}
if (vo[voi] == null)
{
args[ai] = "";
}
else
{
arg = vo[voi].ToString();
if (vo[voi] is MobiusDataType)
{ // use dblink if defined
MobiusDataType mdt = vo[voi] as MobiusDataType;
if (!String.IsNullOrEmpty(mdt.DbLink))
{
arg = mdt.DbLink;
}
}
args[ai] = arg;
}
}
for (ai = 1; ai < args.Count; ai++)
{
arg = args[ai];
if (!Lex.IsDouble(arg) && !arg.StartsWith("'") && !arg.StartsWith("[")) // quote if string
{
arg = Lex.AddSingleQuotes(arg);
}
if (argsString != "")
{
argsString += ",";
}
argsString += arg;
}
string txt = // build full string including link & display value
"<a href=\"http:////Mobius/command?ClickFunction " +
funcName + "(" + argsString + ")\">" + displayValue + "</a>";
return(txt);
}
/// <summary>
/// Format a hyperlink for a metacolumn where details are available
/// </summary>
/// <param name="qc"></param>
/// <param name="mdt"></param>
/// <returns></returns>
public static string FormatDetailsAvailableHyperlink(
QueryColumn qc,
MobiusDataType mdt)
{
string hyperlink, uri = "";
int drilldownLevel = 1;
if (qc == null || qc.MetaColumn == null || qc.MetaColumn.MetaTable == null)
{
return("");
}
MetaColumn mc = qc.MetaColumn;
MetaTable mt = mc.MetaTable;
MetaColumnType mcType = mc.DataType;
MetaBrokerType mbt = qc.MetaColumn.MetaTable.MetaBrokerType;
// Annotation table broker
if (mbt == MetaBrokerType.Annotation)
{
return(mdt.Hyperlink); // just return already formatted hyperlink value
}
// Target-Assay broker
else if (mbt == MetaBrokerType.TargetAssay)
{
if (Lex.Eq(qc.MetaColumn.Name, "assy_nm"))
{
if (mdt.DbLink != "")
{
string[] sa = mdt.DbLink.Split(',');
string mtName = sa[0].Trim() + "_" + sa[1].Trim();
uri = "http://Mobius/command?" +
"ClickFunction ShowTableDescription " + mtName;
}
}
else if (qc.MetaColumn.DetailsAvailable)
{
if (Lex.Eq(qc.MetaColumn.MetaTable.Name, MultiDbAssayDataNames.CombinedNonSumTableName))
{
drilldownLevel = 2; // level 2 goes from unsummarized unpivoted to warehouse details
}
else
{
drilldownLevel = 1; // level 1 goes from summarized to unsummarized (UNPIVOTED_ASSAY_RESULTS)
}
uri = "http://Mobius/command?" +
"ClickFunction DisplayDrilldownDetail " +
qc.MetaColumn.MetaTable.Name + " " + qc.MetaColumn.Name +
" " + drilldownLevel + " " + Lex.AddSingleQuotes(mdt.DbLink);
}
}
// Pivot broker
else if (mbt == MetaBrokerType.Pivot)
{
string[] sa = mdt.DbLink.Split(',');
if (sa.Length < 2 || sa[1] == "")
{
return("");
}
uri = sa[1];
if (uri.ToLower().StartsWith("www."))
{
uri = "http://" + uri; // fix shortcut for proper linking
}
}
// All other broker types
else
{
int nValue = -1;
if (mdt is QualifiedNumber)
{
nValue = ((QualifiedNumber)mdt).NValue;
}
if ((qc.QueryTable.MetaTable.UseSummarizedData && nValue >= 0) || // link if there is a non-null n-value associated with number
qc.MetaColumn.DetailsAvailable) // or we explicitly know that details are available
{
uri = "http://Mobius/command?" +
"ClickFunction DisplayDrilldownDetail " +
qc.QueryTable.MetaTable.Name + " " + qc.MetaColumn.Name +
(qc.QueryTable.MetaTable.UseSummarizedData ? " 1 " : " 2 ") + Lex.AddSingleQuotes(mdt.DbLink);
}
}
return(uri);
}
/// <summary>
/// See if a ClickFunction command & process if so
/// </summary>
/// <param name="command"></param>
/// <param name="qm"></param>
/// <param name="cInf"></param>
public static void Process(
string command,
QueryManager qm,
CellInfo cInf = null)
{
QueryTable rootQt, qt;
QueryColumn qc;
MetaTable mt;
MetaColumn mc;
Query q2;
string dbName = "", mtName = "", mcName = "";
List <string> args0, args;
string funcName, arg1, arg2, arg3, arg4, arg5;
string value = "", keyValue = "";
int ai;
try
{
// Parse click function arguments stripping all single quotes.
// Arguments may be defined in the clickfunction definition including col values
// indicated by field.Value in the metacolumn clickfunction definition.
// If no args are defined in the clickfunction definition then a field value
// argument will be added by default or the keyValue if [keyvalue] appears in the
// ClickFunction definition
CurrentClickQueryManager = qm;
args0 = Lex.ParseAllExcludingDelimiters(command, "( , )", false);
args = new List <string>();
for (ai = 0; ai < args0.Count; ai++) // strip all single quotes
{
string arg = args0[ai];
if (arg.StartsWith("'"))
{
arg = Lex.RemoveSingleQuotes(arg);
}
//if (Lex.Eq(arg, "[rowcol]") && cInf!= null)
//{ // pass grid row & col
// args.Add(cInf.GridRowHandle.ToString());
// args.Add(cInf.GridColAbsoluteIndex.ToString());
//}
//else
args.Add(arg);
}
funcName = args[0];
arg1 = (args.Count >= 2 ? args[1] : ""); // get other args
arg2 = (args.Count >= 3 ? args[2] : "");
arg3 = (args.Count >= 4 ? args[3] : "");
arg4 = (args.Count >= 5 ? args[4] : "");
arg5 = (args.Count >= 6 ? args[5] : "");
if (Lex.Eq(funcName, "DisplayAllData"))
{ // do all data display for supplied root table and key, i.e. DisplayAllData(TableName, KeyColName, KeyValue)
ParseMetaTableMetaColumn(arg1, out mt, arg2, out mc);
string extKey = arg3;
string intKey = CompoundId.Normalize(extKey, mt);
Progress.Show("Building Query...");
_query = QueryEngine.GetSelectAllDataQuery(mt.Name, intKey);
Progress.Show("Retrieving data..."); // put up progress dialog since this may take a while
QbUtil.RunPopupQuery(_query, mt.KeyMetaColumn.Name + " " + extKey);
Progress.Hide();
return;
}
else if (Lex.Eq(funcName, "DisplayAllDataUsingDbName"))
{ // display all data for supplied database synonym & key value, i.e. DisplayAllData2(DataBaseSynonym, KeyValue)
mtName = null;
dbName = arg1;
RootTable rti = RootTable.GetFromTableLabel(dbName);
if (rti != null)
{
mtName = rti.MetaTableName;
}
else // try synonyms
{
DictionaryMx dict = DictionaryMx.Get("Database_Synonyms");
if (dict != null)
{
mtName = dict.LookupDefinition(dbName);
}
}
if (String.IsNullOrEmpty(mtName))
{
MessageBoxMx.ShowError("Unrecognized database: " + dbName);
return;
}
mt = MetaTableCollection.Get(mtName);
if (mt == null)
{
MessageBoxMx.ShowError("Can't find key metatable " + mtName + " for database " + dbName);
return;
}
string extKey = arg2;
string intKey = CompoundId.Normalize(extKey, mt);
Progress.Show("Building Query...");
_query = QueryEngine.GetSelectAllDataQuery(mt.Name, intKey);
Progress.Show("Retrieving data..."); // put up progress dialog since this may take a while
QbUtil.RunPopupQuery(_query, mt.KeyMetaColumn.Name + " " + extKey);
return;
}
// Run a query displaying results to a grid or web page and substituting a parameter value
else if (Lex.Eq(funcName, "RunHtmlQuery") || Lex.Eq(funcName, "RunGridQuery"))
{ // command to display to grid or html
if (arg1.StartsWith("MetaTreeNode=", StringComparison.OrdinalIgnoreCase))
{ // query based on metatables under a tree node
string nodeName = arg1.Substring("MetaTreeNode=".Length).Trim();
_cid = arg2;
MetaTreeNode mtn = MetaTree.GetNode(nodeName);
if (mtn == null)
{
MessageBoxMx.ShowError("Can't find tree node referenced in ClickFunction: " + nodeName);
return;
}
_query = new Query();
MetaTable rootMt = null;
foreach (MetaTreeNode mtn_ in mtn.Nodes)
{
if (!mtn_.IsDataTableType)
{
continue;
}
mt = MetaTableCollection.Get(mtn_.Target);
if (mt == null)
{
continue;
}
if (rootMt == null)
{
rootMt = mt.Root;
rootQt = new QueryTable(_query, rootMt);
}
if (mt == rootMt)
{
continue;
}
qt = new QueryTable(_query, mt);
}
if (_query.Tables.Count == 0)
{
MessageBoxMx.ShowError("No valid data tables found: " + nodeName);
return;
}
_query.KeyCriteria = "= " + _cid;
_title = mtn.Label + " for " + _query.Tables[0].MetaTable.MetaColumns[0].Label + " " + CompoundId.Format(_cid);
}
else if (arg1.StartsWith("Query=", StringComparison.OrdinalIgnoreCase))
{ // query based on saved query
string qIdString = arg1.Substring("Query=".Length).Trim();
if (qIdString.StartsWith("Query_", StringComparison.OrdinalIgnoreCase))
{
qIdString = qIdString.Substring("Query_".Length).Trim();
}
int qId = int.Parse(qIdString);
_query = QbUtil.ReadQuery(qId);
_cid = arg2;
_query.KeyCriteria = "= " + _cid;
_title = _query.UserObject.Name + " for " + _query.Tables[0].MetaTable.MetaColumns[0].Label + " " + CompoundId.Format(_cid);
}
else // explicit mql string to execute
{
_mql = arg1; // mql to execute
if (Lex.IsUndefined(_mql))
{
throw new Exception("Expected MQL query not found: " + command);
}
mt = null;
mc = null;
if (Lex.IsDefined(arg2) && Lex.IsDefined(arg3))
{
mtName = arg2;
mcName = arg3;
value = arg4; // value to plug in to mql
keyValue = value;
ParseMetaTableMetaColumn(arg2, out mt, arg3, out mc);
}
else if (cInf != null)
{
mt = cInf.Mt;
mc = cInf.Mc;
value = cInf?.DataValue?.ToString();
keyValue = qm?.DataTableManager?.GetRowKey(cInf.DataRowIndex);
}
if (mt == null || mc == null)
{
throw new Exception("Invalid MetaTable or MetaColumn name(s): " + command);
}
if (!mc.IsNumeric)
{
value = Lex.AddSingleQuotes(value); // quote if not numeric
}
int i1 = _mql.ToLower().IndexOf("[value]"); // see if a value parameter
if (i1 >= 0)
{
string value2 = value;
_mql = _mql.Replace(_mql.Substring(i1, 7), value);
_title = mc.Label + " " + value;
}
i1 = _mql.ToLower().IndexOf("[keyvalue]"); // see if a key value parameter
if (i1 >= 0)
{
_mql = _mql.Replace(_mql.Substring(i1, 10), keyValue);
_title = mt.KeyMetaColumn.Label + " " + keyValue;
}
try { _query = MqlUtil.ConvertMqlToQuery(_mql); }
catch (Exception ex)
{
MessageBoxMx.ShowError("Error converting Mql to query: " + ex.Message);
return;
}
}
if (Lex.Eq(funcName, "RunHtmlQuery"))
{
QbUtil.RunPopupQuery(_query, _title, OutputDest.Html);
}
else // output to grid
{
QbUtil.RunPopupQuery(_query, _title, OutputDest.WinForms);
}
//else // create new grid query & run (will lose results for current query)
//{
// QbUtil.NewQuery(_title); // show in query builder
// QbUtil.SetCurrentQueryInstance(_query);
// QbUtil.RenderQuery();
// string nextCommand = QueryExec.RunQuery(_query, OutputDest.Grid);
//}
return;
}
// Open a URL, normally substituting parameter value
else if (Lex.Eq(funcName, "OpenUrl"))
{
string url = arg1; // url to execute
value = arg2; // value to plug in to url
int i1 = Lex.IndexOf(url, "[value]"); // fill in one of the value place holders
if (i1 >= 0)
{
string value2 = value;
url = url.Replace(url.Substring(i1, 7), value);
}
else // check to see if we are matching on key
{
i1 = Lex.IndexOf(url, "[keyvalue]");
if (i1 >= 0)
{
url = url.Replace(url.Substring(i1, 10), value);
}
}
SystemUtil.StartProcess(url);
return;
}
else if (Lex.Eq(funcName, "OpenUrlFromSmallWorldCid"))
{
SmallWorldDepictions.OpenUrlFromSmallWorldCid(arg1);
return;
}
else if (Lex.Eq(funcName, "ShowProjectDescription"))
{
string projName = arg1;
QbUtil.ShowProjectDescription(projName);
return;
}
else if (Lex.Eq(funcName, "ShowTableDescription"))
{
mtName = arg1;
QbUtil.ShowTableDescription(mtName);
return;
}
else if (Lex.Eq(funcName, "DisplayDrilldownDetail"))
{ // drill down into a result value
mtName = arg1; // table
mcName = arg2; // column
int level = Int32.Parse(arg3);
string resultId = arg4; // quoted resultId to get
q2 = QueryEngine.GetSummarizationDetailQuery(mtName, mcName, level, resultId);
if (q2 == null)
{
throw new Exception("Unable to build drill-down query for: " + mtName + "." + mcName);
}
bool success = QbUtil.RunPopupQuery(q2, "Result Detail", OutputDest.WinForms);
return;
}
//else if (Lex.Eq(funcName, "PopupSmilesStructure")) // display structure for a Smiles string (still needs some work...)
//{
// string molString = arg1.ToString();
// ChemicalStructure cs = new ChemicalStructure(StructureFormat.Smiles, molString);
// ToolHelper.DisplayStructureInPopupGrid("Title...", "Smiles", "Structure", cs);
//}
//else if (Lex.Eq(funcName, "PopupChimeStructure")) // display structure for a Chime string
//{
// string molString = arg1.ToString();
// ChemicalStructure cs = new ChemicalStructure(StructureFormat.Smiles, molString);
// ToolHelper.DisplayStructureInPopupGrid("Title...", "Smiles", "Structure", cs);
//}
else if (Lex.Eq(funcName, "DisplayWebPage"))
{ // substitute a field value into a url & display associated web page
string url = arg1;
ParseMetaTableMetaColumn(arg2, out mt, arg3, out mc);
value = arg4; // value to plug in to mql
// value = "{6E9C28EF-407E-44A0-9007-5FFB735A5C6C}"; // debug
// value = "{0AC17903-E551-445E-BFAA-860023D2884F}"; // debug
// value = "{63EE71F9-15BA-42FB-AFDC-C399103707B1}"; // debug
// value = "{80591183-B7BA-4669-8C5F-7E7F53D981CE}";
//lex.OpenString(mc.ClickFunction); // reparse url to get proper case
//funcName = lex.GetNonDelimiter();
//url = Lex.RemoveAllQuotes(lex.GetNonDelimiter());
_title = mc.Label + " " + value;
int i1 = url.ToLower().IndexOf("[value]"); // fill in one of the value place holders
if (i1 >= 0)
{
url = url.Replace(url.Substring(i1, 7), value);
}
else // check to see if we are matching on key
{
i1 = url.ToLower().IndexOf("[keyvalue]");
if (i1 >= 0)
{
url = url.Replace(url.Substring(i1, 10), value);
_title = mt.KeyMetaColumn.Label + " " + value;
}
}
UIMisc.ShowHtmlPopupFormDocument(url, _title);
return;
}
else if (Lex.Eq(funcName, "DisplayOracleBlobDocument")) // display a document contained in an Oracle blob column
{ // Syntax: DisplayOracleBlobDocument(<table-to-lookup>, <value_match_column>, <file-name-or-type-col>, <content-column>)
string table = arg1;
string matchCol = arg2;
string typeCol = arg3;
string contentCol = arg4;
string matchVal = arg5; // value to match
try
{
string typeName;
byte[] ba;
UalUtil.SelectOracleBlob(table, matchCol, typeCol, contentCol, matchVal, out typeName, out ba);
if (ba == null || ba.Length == 0)
{
return;
}
UIMisc.SaveAndOpenBinaryDocument(typeName, ba);
}
catch (Exception ex)
{
MessageBoxMx.ShowError("Error retrieving document: " + ex.Message);
return;
}
return;
}
else if (Lex.Eq(funcName, "DisplayOracleClobDocument")) // display a document contained in an Oracle clob column
{ // Syntax: DisplayOracleBlobDocument(<table-to-lookup>, <value_match_column>, <file-name-or-type-col>, <content-column>)
string table = arg1;
string matchCol = arg2;
string typeCol = arg3;
string contentCol = arg4;
string matchVal = arg5; // value to match
try
{
string typeName, clobString;
UalUtil.SelectOracleClob(table, matchCol, typeCol, contentCol, matchVal, out typeName, out clobString);
if (Lex.IsUndefined(clobString))
{
return;
}
UIMisc.SaveAndOpenBase64BinaryStringDocument(typeName, clobString); // assume clob string is a Base64Binary string
}
catch (Exception ex)
{
MessageBoxMx.ShowError("Error retrieving document: " + ex.Message);
return;
}
return;
}
else if (Plugins.IsMethodExtensionPoint(funcName))
{
List <object> objArgs = new List <object>();
for (ai = 1; ai < args.Count; ai++) // build list of object arguments
{
objArgs.Add(args[ai]);
}
Plugins.CallStringExtensionPointMethod(funcName, objArgs);
}
else if (Lex.Eq(funcName, "None")) // dummy click function
{
return;
}
else
{
MessageBoxMx.ShowError("Unrecogized click function: " + funcName);
return;
}
}
catch (Exception ex)
{
Exception ex2 = ex;
if (ex.InnerException != null)
{
ex2 = ex.InnerException;
}
string msg = "Error executing ClickFunction: " + command + "\r\n" +
DebugLog.FormatExceptionMessage(ex);
MessageBoxMx.ShowError(msg);
ServicesLog.Message(msg);
}
}
/// <summary>
/// Invoke the editor
/// </summary>
/// <param name="qc">QueryColumn to edit</param>
/// <returns></returns>
public static bool Edit(
QueryColumn qc)
{
ParsedSingleCriteria psc;
if (qc.MetaColumn.DictionaryMultipleSelect)
{
return(CriteriaDictMultSelect.Edit(qc));
}
if (Instance == null)
{
Instance = new CriteriaMolFormula();
}
if (qc.Criteria != "")
{
psc = MqlUtil.ParseQueryColumnCriteria(qc);
}
else
{
psc = new ParsedSingleCriteria();
psc.OpEnum = CompareOp.FormulaEqual;
}
Instance.Formula.Text = psc.Value;
if (psc.OpEnum == CompareOp.FormulaEqual)
{
Instance.ExactMF.Checked = true;
}
else
{
Instance.PartialMF.Checked = true;
}
DialogResult dr = Instance.ShowDialog(SessionManager.ActiveForm);
if (dr == DialogResult.Cancel)
{
return(false);
}
string val = Instance.Formula.Text.Trim();
if (val == "") // no criteria
{
qc.Criteria = qc.CriteriaDisplay = "";
return(true);
}
if (Instance.ExactMF.Checked)
{
qc.Criteria = qc.MetaColumn.Name + " fmla_eq " + Lex.AddSingleQuotes(val);;
qc.CriteriaDisplay = "= " + val;
}
else
{
qc.Criteria = qc.MetaColumn.Name + " fmla_like " + Lex.AddSingleQuotes(val);
qc.CriteriaDisplay = "like " + val;
}
return(true);
}
/// <summary>
/// When check state changes update the display to reflect
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ItemList_ItemCheck(object sender, DevExpress.XtraEditors.Controls.ItemCheckEventArgs e)
{
if (InSetup)
{
return;
}
InSetup = true;
ItemList.BeginUpdate();
CheckedListBoxItemCollection items = ItemList.Items;
string txt = items[e.Index].Description;
bool isChecked = (e.State == CheckState.Checked);
if (txt == "(All)") // check/uncheck everything
{
foreach (CheckedListBoxItem i in items)
{
i.CheckState = e.State;
}
}
else if (txt == "(Non blanks)")
{
foreach (CheckedListBoxItem i in items)
{
if (i.Description == "(All)" || i.Description == "(Blanks)")
{
continue;
}
i.CheckState = e.State;
}
}
else if (e.State == CheckState.Unchecked) // turned item off; turn off All & Non blanks as well
{
items[AllPos].CheckState = CheckState.Unchecked;
if (NonBlanksPos >= 0 && txt != "(Blanks)")
{
items[NonBlanksPos].CheckState = CheckState.Unchecked;
}
}
if (BlanksPos >= 0) // if blanks allowed set (All) based on Blanks/Non blanks settings
{
if (items[BlanksPos].CheckState == CheckState.Checked &&
items[NonBlanksPos].CheckState == CheckState.Checked)
{
items[AllPos].CheckState = CheckState.Checked;
}
else
{
items[AllPos].CheckState = CheckState.Unchecked;
}
}
ItemList.EndUpdate();
InSetup = false;
// Generate new criteria from set of checks
QueryColumn qc = ColInfo.Qc;
if (items[AllPos].CheckState == CheckState.Checked) // everything
{
qc.SecondaryCriteria = qc.MetaColumn.Name + " in ('(All)')";
qc.SecondaryCriteriaDisplay = qc.ActiveLabel + " in list (All)";
}
//else if (NonBlanksPos >= 0 && items[NonBlanksPos].CheckState == CheckState.Checked)
//{ // just non-null
// qc.SecondaryCriteria = qc.MetaColumn.Name + " is not null";
// qc.SecondaryCriteriaDisplay = qc.ActiveLabel + " is not null";
//}
else // build list of checked items possibly including "(Blanks)" and "(Non blanks)"
{
int itemsChecked = 0;
qc.SecondaryCriteria = qc.MetaColumn.Name + " in (";
foreach (CheckedListBoxItem i in items)
{
string normalizedString;
if (i.CheckState != CheckState.Checked)
{
continue;
}
if (itemsChecked > 0)
{
qc.SecondaryCriteria += ", ";
}
normalizedString = i.Description;
if (i.Description == "(All)" || i.Description == "(Blanks)" || i.Description == "(Non blanks)")
{
} // these are always ok as is
else if (qc.MetaColumn.DataType == MetaColumnType.CompoundId ||
qc.MetaColumn.DataType == MetaColumnType.Date)
{ // store these in internal format
MobiusDataType mdt = MobiusDataType.New(qc.MetaColumn.DataType, i.Description);
normalizedString = mdt.FormatForCriteria();
}
qc.SecondaryCriteria += Lex.AddSingleQuotes(normalizedString);
itemsChecked++;
}
qc.SecondaryCriteria += ")";
qc.SecondaryCriteriaDisplay = qc.ActiveLabel + " in list";
}
qc.SecondaryFilterType = FilterType.CheckBoxList;
QueryManager.QueryResultsControl.UpdateFiltering(ColInfo);
FilterBasicCriteriaControl.SyncBaseQuerySecondaryCriteria(qc); // sync any base query
return;
}
/// <summary>
/// Build query table for decomposition & add to query
/// </summary>
/// <returns></returns>
DialogResult ProcessInput()
{
Query q;
MetaTable mt;
MetaColumn mc;
QueryTable qt;
QueryColumn qc;
q = QbUtil.Query;
if (q == null || q.Tables.Count == 0)
{
MessageBoxMx.ShowError("No current query.");
return(DialogResult.None);
}
qt = q.GetQueryTableByName("Rgroup_Decomposition");
bool newTable = false;
if (qt == null)
{
mt = MetaTableCollection.GetWithException("Rgroup_Decomposition");
qt = new QueryTable(mt);
newTable = true;
}
qc = qt.GetQueryColumnByNameWithException("Core");
MoleculeMx core = new MoleculeMx(MoleculeFormat.Molfile, QueryMolCtl.MolfileString);
if (core.AtomCount == 0)
{
MessageBoxMx.ShowError("A Core structure with R-groups must be defined.");
return(DialogResult.None);
}
qc.MolString = core.GetMolfileString(); // put core structure into table criteria
qc.CriteriaDisplay = "Substructure search (SSS)";
qc.Criteria = "CORE SSS SQUERY";
if (!Structure.Checked && !Smiles.Checked && !Formula.Checked &&
!Weight.Checked && !Index.Checked)
{
MessageBoxMx.ShowError("At least one substituent display format must be selected.");
return(DialogResult.None);
}
qc = qt.GetQueryColumnByName("R1_Structure");
if (ShowCoreStructure.Checked)
{
qc.Label = "R-group, Core\tChime=" + core.GetChimeString(); // reference core in query col header label
qc.MetaColumn.Width = 25;
}
SetSelected(qt, "R1_Structure", Structure.Checked);
SetSelected(qt, "R1_Smiles", Smiles.Checked);
SetSelected(qt, "R1_Formula", Formula.Checked);
SetSelected(qt, "R1_Weight", Weight.Checked);
SetSelected(qt, "R1_SubstNo", Index.Checked);
string terminateOption = TerminateOption.Text;
qc = qt.GetQueryColumnByName("Terminate_Option");
if (qc != null && Lex.IsDefined(terminateOption))
{
qc.Criteria = qt.MetaTable.Name + " = " + Lex.AddSingleQuotes(terminateOption);
qc.CriteriaDisplay = "= " + Lex.AddSingleQuotes(terminateOption);
}
else
{
qc.Criteria = qc.CriteriaDisplay = ""; // not defined
}
if (newTable)
{
q.AddQueryTable(qt); // add to query if new
}
QbUtil.RenderQuery(); // show it
UsageDao.LogEvent("RgroupDecomposition");
return(DialogResult.OK);
}
/// <summary>
/// OK button clicked, process input
/// </summary>
/// <returns></returns>
DialogResult ProcessInput()
{
int rgCount; // number of Rgroups
List <Rgroup> rgList; // list of existing Rgroups
bool[] rgExists; // entry = true if rgroup exists in core
Rgroup rg;
bool oneD, twoD; // matrix dimensionality
List <string> keys = null;
Dictionary <string, List <QualifiedNumber> > mElem; // matrix element dictionary
List <RgroupSubstituent>[] rSubs; // substituents seen for each Rgroup
Query q, q0, q2;
QueryTable qt, qt2;
QueryColumn qc, qc2;
MetaTable mt, mt2;
MetaColumn mc, mc2;
DataTableMx dt;
DataRowMx dr;
DialogResult dlgRslt;
string tok;
int ri, rii, si, qti, qci, bi, bi2;
// Get core structure & list of R-groups
MoleculeMx core = new MoleculeMx(MoleculeFormat.Molfile, SQuery.MolfileString);
if (core.AtomCount == 0)
{
MessageBoxMx.ShowError("A Core structure with R-groups must be defined");
return(DialogResult.None);
}
if (!Structure.Checked && !Smiles.Checked && !Formula.Checked &&
!Weight.Checked && !Index.Checked)
{
MessageBoxMx.ShowError("At least one substituent display format must be selected.");
return(DialogResult.None);
}
mt = MetaTableCollection.GetWithException("Rgroup_Decomposition");
qt = new QueryTable(mt);
qc = qt.GetQueryColumnByNameWithException("Core");
qc.MolString = core.GetMolfileString(); // put core structure into table criteria
qc.CriteriaDisplay = "Substructure search (SSS)";
qc.Criteria = "CORE SSS SQUERY";
qc = qt.GetQueryColumnByNameWithException("R1_Structure");
if (ShowCoreStructure.Checked)
{
qc.Label = "R-group, Core\tChime=" + core.GetChimeString(); // reference core in query col header label
qc.MetaColumn.Width = 25;
}
RgroupDecomposition.SetSelected(qt, "R1_Structure", Structure.Checked); // select for retrieval if checked
RgroupDecomposition.SetSelected(qt, "R1_Smiles", Smiles.Checked);
RgroupDecomposition.SetSelected(qt, "R1_Formula", Formula.Checked);
RgroupDecomposition.SetSelected(qt, "R1_Weight", Weight.Checked);
RgroupDecomposition.SetSelected(qt, "R1_SubstNo", Index.Checked);
string terminateOption = "First mapping"; // terminate on first complete match
qc = qt.GetQueryColumnByNameWithException("Terminate_Option");
qc.Criteria = qt.MetaTable.Name + " = " + Lex.AddSingleQuotes(terminateOption);
qc.CriteriaDisplay = "= " + Lex.AddSingleQuotes(terminateOption);
QueryTable rgdQt = qt; // keep a ref to it
if (QbUtil.Query == null || QbUtil.Query.Tables.Count == 0)
{
MessageBoxMx.ShowError("No current query.");
return(DialogResult.None);
}
q0 = QbUtil.Query; // original query this analysis is based on
q = q0.Clone(); // make copy of source query we can modify
q.SingleStepExecution = false;
qti = 0;
while (qti < q.Tables.Count) // deselect query columns that we don't want
{
qt = q.Tables[qti];
if (Lex.Eq(qt.MetaTable.Name, "Rgroup_Decomposition"))
{ // remove any rgroup decomp table
qti++;
continue;
}
mt = qt.MetaTable;
if (mt.MultiPivot || // check for tables not allowed in underlying query
mt.MetaBrokerType == MetaBrokerType.CalcField || // (called ShouldPresearchAndTransform previously)
mt.MetaBrokerType == MetaBrokerType.MultiTable ||
mt.MetaBrokerType == MetaBrokerType.RgroupDecomp)
{
MessageBoxMx.ShowError("Multipivot/Rgroup table \"" + qt.ActiveLabel +
"\" can't be included in an underlying Rgroup Matrix query");
return(DialogResult.None);
}
for (qci = 0; qci < qt.QueryColumns.Count; qci++)
{
qc = qt.QueryColumns[qci];
if (qc.MetaColumn == null)
{
continue;
}
switch (qc.MetaColumn.DataType)
{
case MetaColumnType.CompoundId: // keep only these
case MetaColumnType.Integer:
case MetaColumnType.Number:
case MetaColumnType.QualifiedNo:
case MetaColumnType.String:
break;
default:
qc.Selected = false;
break;
}
}
qti++;
}
q.AddQueryTable(rgdQt); // Add Rgroup decom table to end of cloned source query
Progress.Show("Retrieving data...");
try
{
dlgRslt = ToolHelper.ExecuteQuery(ref q, out keys);
if (dlgRslt != DialogResult.OK)
{
return(dlgRslt);
}
}
catch (Exception ex)
{
MessageBoxMx.ShowError("Error executing query:\r\n" + ex.Message);
return(DialogResult.None);
}
if (keys == null || keys.Count == 0)
{
Progress.Hide();
MessageBoxMx.ShowError("No results were returned by the query.");
return(DialogResult.None);
}
// Scan modified query to get list of rgroup indexes that are present
rgExists = new bool[32];
rgList = new List <Rgroup>();
QueryTable rgQt = q.GetQueryTableByName("Rgroup_Decomposition");
foreach (QueryColumn qc0 in rgQt.QueryColumns)
{
mc = qc0.MetaColumn;
if (!(mc.Name.StartsWith("R") && mc.Name.EndsWith("_STRUCTURE") && qc0.Selected))
{
continue; // skip if not a selected Rgroup structure
}
int len = mc.Name.Length - ("R" + "_STRUCTURE").Length;
tok = mc.Name.Substring(1, len);
if (!int.TryParse(tok, out ri))
{
continue;
}
rgExists[ri - 1] = true;
rg = new Rgroup();
rg.RIndex = ri;
rg.VoPos = qc0.VoPosition;
rgList.Add(rg);
}
for (bi = 1; bi < rgList.Count; bi++)
{ // sort by increasing R index
rg = rgList[bi];
for (bi2 = bi - 1; bi2 >= 0; bi2--)
{
if (rg.RIndex >= rgList[bi2].RIndex)
{
break;
}
rgList[bi2 + 1] = rgList[bi2];
}
rgList[bi2 + 1] = rg;
}
rgCount = rgList.Count;
twoD = TwoD.Checked;
if (rgCount == 1)
{
twoD = false; // if only 1 rgroup can't do as 2d
}
oneD = !twoD;
// Read data into mElem and rgroup substituents into rSubs.
// Matrix mElem is keyed on [R1Smiles, R2Smiles,... RnSmiles, FieldName] for 1d and
// [R1Smiles, R2Smiles,... FieldName, RnSmiles] for 2d
QueryManager qm = q.QueryManager as QueryManager;
DataTableManager dtm = qm.DataTableManager;
dt = qm.DataTable;
mElem = new Dictionary <string, List <QualifiedNumber> >(); // matrix element dictionary
rSubs = new List <RgroupSubstituent> [32]; // list of substituents seen for each Rgroup
for (rii = 0; rii < rgCount; rii++) // alloc substituent list for rgroup
{
rSubs[rii] = new List <RgroupSubstituent>();
}
int rowCount = 0;
while (true)
{ // scan data accumulating rgroup substituents and data values
dr = dtm.FetchNextDataRow();
if (dr == null)
{
break;
}
rowCount++;
string cid = dr[dtm.KeyValueVoPos] as string;
string lastMapCid = "", rgroupKey = "", rgroupKeyLast = "";
int mapCount = 0;
for (rii = 0; rii < rgCount; rii++) // for
{
MoleculeMx rSub = dr[rgList[rii].VoPos] as MoleculeMx;
if (rSub == null || rSub.AtomCount == 0)
{
continue;
}
ri = rgList[rii].RIndex; // actual R index in query
int subIdx = RgroupSubstituent.Get(rSub, rSubs[rii]);
// if (ri == 1 && subIdx != 0) subIdx = subIdx; // debug
if (subIdx < 0)
{
continue;
}
string rKey = "R" + ri.ToString() + "_" + (subIdx + 1).ToString();
if (oneD || rii < rgCount - 1)
{
if (rgroupKey != "")
{
rgroupKey += "\t";
}
rgroupKey += rKey;
}
else
{
rgroupKeyLast = rKey;
}
lastMapCid = cid;
mapCount++;
}
if (lastMapCid == cid) // add the data if compound has a mapping
{
AccumulateMatrixElements(mElem, q, dr, rgroupKey, rgroupKeyLast, cid);
}
if (Progress.IsTimeToUpdate)
{
Progress.Show("Retrieving data: " + StringMx.FormatIntegerWithCommas(rowCount) + " rows...");
}
}
if (rowCount == 0)
{
Progress.Hide();
MessageBoxMx.ShowError("No data rows retrieved");
return(DialogResult.None);
}
if (twoD && (rSubs[rgCount - 1] == null || rSubs[rgCount - 1].Count == 0))
{ // if 2D be sure we have at least one substituent for the last Rgroup
Progress.Hide();
MessageBoxMx.ShowError("No substituents found for R" + rgCount.ToString());
return(DialogResult.None);
}
// Create a MetaTable & DataTable for matrix results
Progress.Show("Analyzing data...");
mt = new MetaTable(); // create output table
MatrixCount++;
mt.Name = "RGROUPMATRIX_" + MatrixCount;
mt.Label = "R-group Matrix " + MatrixCount;
mt.MetaBrokerType = MetaBrokerType.RgroupDecomp;
mc = // use sequence for key
mt.AddMetaColumn("RgroupMatrixId", "No.", MetaColumnType.Integer, ColumnSelectionEnum.Selected, 3);
mc.ClickFunction = "None"; // avoid hyperlink on this key
mc.IsKey = true;
int maxLeftR = rgCount;
if (twoD)
{
maxLeftR = rgCount - 1;
}
for (ri = 0; ri < maxLeftR; ri++)
{
string rStr = "R" + (ri + 1).ToString();
if (Structure.Checked)
{
mc = mt.AddMetaColumn(rStr + "Str", rStr, MetaColumnType.Structure, ColumnSelectionEnum.Selected, 12);
if (ri == 0 && ShowCoreStructure.Checked) // include core structure above R1 if requested
{
string chimeString = MoleculeMx.MolfileStringToSmilesString(SQuery.MolfileString);
mc.Label = "R1, Core\tChime=" + chimeString;
mc.Width = 25;
}
}
if (Smiles.Checked)
{
mc = mt.AddMetaColumn(rStr + "Smi", rStr + " Smiles", MetaColumnType.String, ColumnSelectionEnum.Selected, 12);
}
if (Formula.Checked)
{
mc = mt.AddMetaColumn(rStr + "Mf", rStr + " Formula", MetaColumnType.String, ColumnSelectionEnum.Selected, 8);
}
if (Weight.Checked)
{
mc = mt.AddMetaColumn(rStr + "MW", rStr + " Mol. Wt.", MetaColumnType.Number, ColumnSelectionEnum.Selected, 6, ColumnFormatEnum.Decimal, 2);
}
if (Index.Checked)
{
mc = mt.AddMetaColumn(rStr + "Index", rStr + " Subst. Idx.", MetaColumnType.Number, ColumnSelectionEnum.Selected, 4);
mc.Format = ColumnFormatEnum.Decimal;
}
}
mc = // add column to contain result type
mt.AddMetaColumn("ResultType", "Result Type", MetaColumnType.String, ColumnSelectionEnum.Selected, 12);
if (oneD) // add just 1 column to contain results
{
mc = mt.AddMetaColumn("Results", "Results", MetaColumnType.QualifiedNo, ColumnSelectionEnum.Selected, 12);
mc.MetaBrokerType = MetaBrokerType.RgroupDecomp; // broker to do special col handling for cond formtting
if (QbUtil.Query.UserObject.Id > 0)
{
mc.DetailsAvailable = true;
}
}
else // add col for each substituent for last rgroup
{
string rStr = "R" + rgCount.ToString();
for (si = 0; si < rSubs[rgCount - 1].Count; si++)
{
string cName = rStr + "_" + (si + 1).ToString();
string cLabel = cName.Replace("_", ".");
RgroupSubstituent rgs = rSubs[ri][si]; // get substituent info
if (Structure.Checked) // include structure
{
cLabel += "\tChime=" + rgs.Struct.GetChimeString();
}
else if (Smiles.Checked)
{
cLabel += " = " + rgs.Struct.GetSmilesString();
}
else if (Formula.Checked)
{
cLabel += " = " + rgs.Struct.MolFormula;
}
else if (Weight.Checked)
{
cLabel += " = " + rgs.Struct.MolWeight;
}
else if (Index.Checked)
{
cLabel += " = " + (si + 1).ToString();
}
mc = mt.AddMetaColumn(cName, cLabel, MetaColumnType.QualifiedNo, ColumnSelectionEnum.Selected, 12);
mc.MetaBrokerType = MetaBrokerType.RgroupDecomp;
if (QbUtil.Query.UserObject.Id > 0)
{
mc.DetailsAvailable = true;
}
}
}
MetaTableCollection.UpdateGlobally(mt); // add as a known metatable
if (mElem.Count == 0) // be sure we have a matrix
{
Progress.Hide();
MessageBoxMx.ShowError("No matrix can be created because insufficient data was found.");
return(DialogResult.None);
}
// Build the DataTable
Progress.Show("Building data table...");
q2 = new Query(); // build single-table query to hold matrix
qt2 = new QueryTable(q2, mt);
dt = DataTableManager.BuildDataTable(q2);
Dictionary <string, List <QualifiedNumber> > .KeyCollection kc = mElem.Keys;
string[] rgKeys = new string[mElem.Count];
kc.CopyTo(rgKeys, 0);
Array.Sort(rgKeys);
string[] rgKey = null, lastRgKey = null;
int rki = 0;
for (rki = 0; rki < rgKeys.Length; rki++)
{
rgKey = rgKeys[rki].Split('\t');
int riTop = rgCount + 1; // all r substituents & field name on left
if (twoD)
{
riTop = rgCount;
}
for (ri = 0; ri < riTop; ri++) // see if any changes in left side substituents or field name
{
if (lastRgKey == null || rgKey[ri] != lastRgKey[ri])
{
break;
}
}
if (ri < riTop || oneD) // if 2d then new row only if some change before last R
{
dr = dt.NewRow();
dt.Rows.Add(dr);
dr[dtm.KeyValueVoPos + 1] = new NumberMx(dt.Rows.Count); // integer row key
}
if (!HideRepeatingSubstituents.Checked)
{
ri = 0; // start at first if not hiding
}
lastRgKey = rgKey;
for (ri = ri; ri < riTop; ri++) // build row with these
{
string rgSub = rgKey[ri]; // get substituent id or table.column name
if (rgSub == "")
{
continue;
}
if (ri < riTop - 1)
{ // output substituent and/or smiles
string rStr = "R" + (ri + 1).ToString();
si = rgSub.IndexOf("_");
si = Int32.Parse(rgSub.Substring(si + 1)) - 1; // get substituent index
RgroupSubstituent rgs = rSubs[ri][si]; // get substituent info
if (Structure.Checked)
{
qc2 = qt2.GetQueryColumnByName(rStr + "Str");
dr[QcToDcName(qc2)] = rgs.Struct;
}
if (Smiles.Checked)
{
qc2 = qt2.GetQueryColumnByName(rStr + "Smi");
dr[QcToDcName(qc2)] = new StringMx(rgs.Struct.GetSmilesString());
}
if (Formula.Checked)
{
qc2 = qt2.GetQueryColumnByName(rStr + "Mf");
dr[QcToDcName(qc2)] = new StringMx(rgs.Struct.MolFormula);
}
if (Weight.Checked)
{
qc2 = qt2.GetQueryColumnByName(rStr + "Mw");
dr[QcToDcName(qc2)] = new NumberMx(rgs.Struct.MolWeight);
}
if (Index.Checked)
{
qc2 = qt2.GetQueryColumnByName(rStr + "Index");
dr[QcToDcName(qc2)] = new NumberMx(si + 1);
}
}
else // output field name
{
string[] sa = rgSub.Split('.'); // get field name
qt = q.GetQueryTableByName(sa[0]);
qc = qt.GetQueryColumnByName(sa[1]);
string fieldName = qc.ActiveLabel;
if (q0.Tables.Count >= 3) // qualify by table if 3 or more tables in original query
{
fieldName = qt.ActiveLabel + " - " + fieldName;
}
qc2 = qt2.GetQueryColumnByName("ResultType");
dr[QcToDcName(qc2)] = new StringMx(fieldName);
}
}
// Output value
string cName;
if (oneD)
{
cName = "Results";
}
else
{
cName = rgKey[rgCount]; // get key for this substituent (e.g. R2_1)
}
if (Lex.IsUndefined(cName))
{
continue; // may be no substituent match
}
qc2 = qt2.GetQueryColumnByName(cName);
QualifiedNumber qn = SummarizeData(mElem[rgKeys[rki]]); // get summarized value
dr[QcToDcName(qc2)] = qn;
}
ToolHelper.DisplayData(q2, dt, true);
UsageDao.LogEvent("RgroupMatrix");
Progress.Hide();
return(DialogResult.OK);
}
/// <summary>
/// Get values from form and update criteria in associated QueryColumn
/// </summary>
/// <returns></returns>
bool UpdateCriteria()
{
MetaTable mt;
string label, name, listString;
bool summary;
int tableId;
DataRowCollection rows = ItemGridDataTable.Rows;
string s = "";
List <string> sl2 = new List <string>(new string[] { "", "" });
foreach (DataRow dr in rows)
{
label = sl2[0] = dr["LabelColumn"] as string;
name = sl2[1] = dr["InternalNameColumn"] as string;
if (Lex.IsUndefined(label) && Lex.IsUndefined(name))
{
continue;
}
string txt = Csv.JoinCsvString(sl2);
s += txt + "\r\n";
}
List <MetaTableItem> list = MetaTableItem.ParseList(s, true, true);
string valueList = ""; // list of values to match in criteria
string displayList = "";
string mcDict = Qc.MetaColumn.Dictionary;
bool searchId = Lex.EndsWith(mcDict, ".Id");
bool searchLabel = Lex.EndsWith(mcDict, ".Label");
foreach (MetaTableItem mti in list)
{
mt = MetaTableCollection.Get(mti.InternalName);
if (mt == null)
{
continue;
}
MetaTable.ParseMetaTableName(mt.Name, out tableId, out summary);
label = MetaTable.RemoveSuffixesFromName(mt.Label);
//mtn = MetaTree.GetNode(mti.InternalName); // need to check node name vs target?
// Append to value list
if (valueList.Length > 0)
{
valueList += ", ";
}
if (searchId) // extract numeric id for table if matching against numeric column
{
valueList += tableId.ToString();
}
else
{
valueList += Lex.AddSingleQuotes(label);
}
// Append to label list
if (displayList.Length == 0) // include first item label only in CriteriaDisplay
{
displayList = label + " - ASSAY: ";
}
else
{
displayList += ", ";
}
displayList += tableId.ToString();
}
if (valueList == "")
{
Qc.Criteria = Qc.CriteriaDisplay = "";
}
else
{
Qc.Criteria = Qc.MetaColumn.Name + " in (" + valueList + ")";
Qc.CriteriaDisplay = displayList;
}
return(true);
}
/// <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);
}
/// <summary>
/// Get criteria & validate
/// </summary>
bool GetCriteria()
{
string op = null, val, val2, eval, lim1, lim2, elim1, elim2, txt;
bool allowSpaceDelimiters;
QueryColumn qc = Qc;
MetaColumn mc = qc.MetaColumn;
if (BasicOp.Checked)
{
if (BasicOpBut.Text == "Equals")
{
op = "=";
}
else if (BasicOpBut.Text == "<")
{
op = "<";
}
else if (BasicOpBut.Text == UnicodeString.LessOrEqual)
{
op = "<=";
}
else if (BasicOpBut.Text == UnicodeString.NotEqual)
{
op = "<>";
}
else if (BasicOpBut.Text == ">")
{
op = ">";
}
else if (BasicOpBut.Text == UnicodeString.GreaterOrEqual)
{
op = ">=";
}
val = Value.Text;
if ((eval = CheckCriteriaValue(qc, val)) == null)
{
return(false);
}
qc.CriteriaDisplay = op + " " + val;
qc.Criteria = mc.Name + " " + op + " " + eval;
}
else if (InList.Checked)
{
val = ValueList.Text;
StringBuilder listSql = new StringBuilder();
if (qc.MetaColumn.Dictionary == "") // if no dictionary allow spaces to be used as delimiters
{
allowSpaceDelimiters = true;
}
else
{
allowSpaceDelimiters = false;
}
List <string> list = Csv.SplitCsvString(val, allowSpaceDelimiters);
eval = null;
foreach (string item in list)
{
if ((eval = CheckCriteriaValue(qc, item)) == null)
{
return(false);
}
if (listSql.Length > 0)
{
listSql.Append(",");
}
listSql.Append(eval);
}
qc.Criteria = mc.Name + " in (" + listSql.ToString() + ")";
string listText = Csv.JoinCsvString(list); // and reformat properly for display
qc.CriteriaDisplay = listText;
}
else if (Between.Checked)
{
lim1 = Limit1.Text;
lim2 = Limit2.Text;
if ((elim1 = CheckCriteriaValue(qc, lim1)) == null)
{
return(false);
}
if ((elim2 = CheckCriteriaValue(qc, lim2)) == null)
{
return(false);
}
qc.CriteriaDisplay = "Between " + lim1 + " and " + lim2;
qc.Criteria = mc.Name + " BETWEEN " + elim1 + " AND " + elim2;
}
else if (Like.Checked)
{
val = Substring.Text;
if ((eval = CheckCriteriaValue(qc, val)) == null)
{
return(false);
}
eval = Lex.RemoveSingleQuotes(eval);
if (val.IndexOf("%") < 0 && val.IndexOf("_") < 0)
{
eval = "%" + eval + "%"; // need to add wildcards?
}
eval = Lex.AddSingleQuotes(eval);
qc.CriteriaDisplay = "Contains " + val;
qc.Criteria = mc.Name + " LIKE " + eval;
}
else if (Within.Checked)
{
val = WithinValue.Text;
val2 = WithinUnits.Text;
if (!Lex.IsDouble(val))
{
MessageBoxMx.Show(val + " is not a valid number", UmlautMobius.String,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(false);
}
qc.CriteriaDisplay = "Within the last " + val + " " + val2;
qc.Criteria = mc.Name + " WITHIN " + val + " " + val2;
}
else if (IsNotNull.Checked)
{
qc.CriteriaDisplay = "Exists";
qc.Criteria = mc.Name + " IS NOT NULL";
}
else if (IsNull.Checked)
{
qc.CriteriaDisplay = "Doesn't exist";
qc.Criteria = mc.Name + " IS NULL";
}
else if (All.Checked)
{
qc.CriteriaDisplay = "All data rows";
qc.Criteria = "(" + mc.Name + " IS NOT NULL OR " + mc.Name + " IS NULL)";
}
else if (Instance.None.Checked)
{
qc.CriteriaDisplay = "";
qc.Criteria = "";
}
else
{
throw new Exception("Unexpected criteria form operator");
}
return(true); // success if no errors
}
/// <summary>
/// Parse an operator and value
/// </summary>
/// <param name="qc"></param>
/// <param name="op"></param>
/// <param name="val"></param>
/// <returns>Expert form of criteria if form is acceptable</returns>
static string CheckCriteriaValue(
QueryColumn qc,
string val)
{
string eval, txt, txt2, txt3;
int i1;
double d1;
MetaColumn mc = qc.MetaColumn;
if (val == null)
{
return(null);
}
val = eval = val.Trim();
switch (mc.DataType)
{
case MetaColumnType.Date:
eval = DateTimeMx.Normalize(val);
if (eval == null)
{
MessageBoxMx.Show(val + " is not a valid date", UmlautMobius.String,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(null);
}
break;
case MetaColumnType.Integer:
case MetaColumnType.Number:
case MetaColumnType.QualifiedNo:
try
{ d1 = Convert.ToDouble(val); }
catch (Exception e)
{
MessageBoxMx.Show(val + " is not a valid number", UmlautMobius.String,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(null);
}
break;
case MetaColumnType.String: // quoted form
case MetaColumnType.MolFormula:
eval = Lex.AddSingleQuotes(Lex.RemoveSingleQuotes(val));
break;
case MetaColumnType.DictionaryId: // translate dictionary value back to database value
eval = DictionaryMx.LookupDefinition(mc.Dictionary, val); // get database value
if (eval == null || eval == "")
{
MessageBoxMx.Show(Lex.Dq(val) + " is not a valid value.\nYou must select an item from the dropdown box.", UmlautMobius.String,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return(null);
}
eval = Lex.AddSingleQuotes(Lex.RemoveSingleQuotes(eval)); // quote in case string value
break;
} // end of datatype case
return(eval);
} // end of CheckCriteriaValue
/// <summary>
/// Convert labeled criteria to complex checking for errors
/// </summary>
/// <param name="criteria"></param>
/// <param name="q"></param>
/// <param name="complexCriteriaCtl"></param>
public static bool ConvertEditableCriteriaToComplex(
LabeledCriteria labeledCriteria,
Query q,
RichTextBox complexCriteriaCtl)
{
QueryTable qt;
QueryColumn qc;
Lex lex = new Lex();
lex.SetDelimiters(" , ; ( ) < = > <= >= <> != !> !< [ ]");
lex.OpenString(labeledCriteria.Text);
StringBuilder sb = new StringBuilder();
PositionedToken lastTok = null;
List <PositionedToken> tokens = new List <PositionedToken>(); // list of tokens seen
int parenDepth = 0;
while (true)
{
PositionedToken pTok = lex.GetPositionedToken();
if (pTok == null)
{
break;
}
tokens.Add(pTok);
if (lastTok != null)
{ // include same white space between tokens
int wsBeg = lastTok.Position + lastTok.Text.Length;
sb.Append(labeledCriteria.Text.Substring(wsBeg, pTok.Position - wsBeg));
}
string tok = pTok.Text;
if (MqlUtil.IsKeyWord(pTok.Text)) // ok if keyword, operator, etc
{
if (pTok.Text == "(")
{
parenDepth++;
}
else if (pTok.Text == ")")
{
parenDepth--;
}
}
else if (pTok.Text.StartsWith("'")) // string constant
{
if (tokens.Count >= 3 && Lex.Eq(tokens[tokens.Count - 2].Text, "In") &&
Lex.Eq(tokens[tokens.Count - 1].Text, "List"))
{ // saved list reference
UserObject uo = QueryEngine.ResolveCnListReference(tok);
if (uo != null)
{
tok = "CNLIST_" + uo.Id.ToString();
}
else
{
tok = "Nonexistant list";
}
tok = Lex.AddSingleQuotes(tok);
}
}
else if (Lex.IsDouble(pTok.Text))
{
} // numeric constant
else if (tok == "[")
{ // translate editable structure reference
pTok = lex.GetPositionedToken();
if (!MatchToken(pTok, "Edit", complexCriteriaCtl))
{
return(false);
}
pTok = lex.GetPositionedToken();
if (!MatchToken(pTok, "Structure", complexCriteriaCtl))
{
return(false);
}
pTok = lex.GetPositionedToken();
tok = Lex.RemoveSingleQuotes(pTok.Text.ToUpper());
if (!labeledCriteria.Structures.ContainsKey(tok))
{
ConvertLabeledCriteriaError("Structure \"" + pTok.Text + "\" not defined", pTok, complexCriteriaCtl);
return(false);
}
tok = Lex.AddSingleQuotes(labeledCriteria.Structures[tok]); // replace with chime
pTok = lex.GetPositionedToken();
if (!MatchToken(pTok, "]", complexCriteriaCtl))
{
return(false);
}
}
else if (Lex.Eq(pTok.Text, "structure_field"))
{ // check for user failing to define structure_field in structure search criteria
ConvertLabeledCriteriaError("\"Structure_field\" must be replaced with a real field name", pTok, complexCriteriaCtl);
return(false);
}
else // must be a column reference or invalid token
{ // translate labeled column name
tok = TranslateLabeledColumnName(pTok, q, complexCriteriaCtl);
if (tok == null)
{
return(false);
}
}
sb.Append(tok);
lastTok = pTok;
}
tokens = tokens; // debug
if (parenDepth != 0) // parens balance?
{
if (parenDepth > 0)
{
MessageBoxMx.ShowError("Unbalanced parentheses: left parentheses exceed right by " + parenDepth.ToString());
}
else
{
MessageBoxMx.ShowError("Unbalanced parentheses: right parentheses exceed left by " + (-parenDepth).ToString());
}
if (complexCriteriaCtl != null)
{
complexCriteriaCtl.Focus();
}
return(false);
}
q.ComplexCriteria = sb.ToString(); // store back in query
return(true);
}
/// 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;
}
private void OK_Click(object sender, EventArgs e)
{
int CnCount;
string listName, listText, errMsg = "", tok, cidLo, cidHi, cid = null, cid2 = null;
QueryColumn qc = Qc;
MetaColumn mc = qc.MetaColumn;
MetaTable mt = mc.MetaTable;
// Perform validity check on form
// Equality
if (EQ.Checked)
{
tok = Cid.Text.ToUpper();
if (tok == "")
{
XtraMessageBox.Show("Single compound value is missing", UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.None);
Cid.Focus();
return;
}
if (SS.I.ValidateCompoundIds)
{
if (tok == "")
{
errMsg = "You must supply a " + qc.ActiveLabel;
}
else if (RootTable.IsStructureDatabaseRootTable(mt.Root))
{
if (!CompoundId.IsValidForDatabase(tok, mt))
{
errMsg = tok + " is not a valid " + qc.ActiveLabel;
}
else
{
cid = CompoundId.Normalize(tok, mt);
tok = CompoundId.Format(cid, mt);
errMsg = "";
}
}
else
{
cid = tok; // use as is
errMsg = ""; // no error
}
if (errMsg != "")
{
MessageBoxMx.Show(errMsg, UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
qc.CriteriaDisplay = "= " + tok;
qc.Criteria = mc.Name + " = " + Lex.AddSingleQuotes(cid);
CnCount = 1;
}
// List
else if (InList.Checked)
{
listText = CidListString.Trim();
if (listText == "")
{
XtraMessageBox.Show("The list must contain one or more items", UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.None);
if (CidListDisplayIsEditable())
{
CidListDisplay.Focus();
}
return;
}
qc.CriteriaDisplay = CidList.FormatAbbreviatedCidListForDisplay(qc, listText);
qc.Criteria = mc.Name + " IN (" + listText + ")";
}
// Current list
else if (TempList.Checked)
{
qc.CriteriaDisplay = "In temporary list: " + TempListName.Text;
qc.Criteria = mc.Name + " IN LIST " + UserObject.TempFolderNameQualified + TempListName.Text;
CnCount = SessionManager.CurrentResultKeysCount;
}
// Saved list
else if (SavedList.Checked)
{
if (ListName.Text.Trim() == "")
{
XtraMessageBox.Show("A list name must be supplied", UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.None);
ListName.Focus();
return;
}
string internalName = ResolveSavedListUo();
if (internalName == null)
{
return;
}
qc.CriteriaDisplay = "In list: " + SavedListUo.Name;
qc.Criteria = mc.Name + " IN LIST " + Lex.AddSingleQuotes("CNLIST_" + SavedListUo.Id.ToString()); // quote list name
CnCount = 1; // may be larger
}
// Between
else if (Between.Checked)
{
cidLo = CidLo.Text.Trim();
cidHi = CidHi.Text.Trim();
if (cidLo == "" || cidHi == "")
{
XtraMessageBox.Show("Between value is missing", UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.None);
if (CidLo.Text.Trim() == "")
{
CidLo.Focus();
}
else if (CidHi.Text.Trim() == "")
{
CidHi.Focus();
}
return;
}
if (SS.I.ValidateCompoundIds)
{
errMsg = "";
if (cidLo == "")
{
errMsg = "You must supply a " + qc.ActiveLabel;
}
else
{
if (!CompoundId.IsValidForDatabase(cidLo, mt))
{
errMsg = cidLo + " is not a valid " + qc.ActiveLabel;
}
else
{
cid = CompoundId.Normalize(cidLo, mt);
cidLo = CompoundId.Format(cid);
}
}
if (errMsg != "")
{
MessageBoxMx.Show(errMsg, UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
else
{
cid = cidLo; // use as is
}
if (SS.I.ValidateCompoundIds)
{
errMsg = "";
if (cidHi == "")
{
errMsg = "You must supply a " + qc.ActiveLabel;
}
else
{
if (!CompoundId.IsValidForDatabase(cidHi, mt))
{
errMsg = cidHi + " is not a valid " + qc.ActiveLabel;
}
else
{
cid2 = CompoundId.Normalize(cidHi, mt);
cidHi = CompoundId.Format(cid2);
}
}
if (errMsg != "")
{
MessageBoxMx.Show(errMsg, UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
else
{
cid2 = cidHi; // use as is
}
qc.CriteriaDisplay = "Between " + cidLo + " and " + cidHi;
qc.Criteria = mc.Name + " BETWEEN " + Lex.AddSingleQuotes(cid) + " AND " + Lex.AddSingleQuotes(cid2);
}
else if (None.Checked)
{
qc.CriteriaDisplay = "";
qc.Criteria = "";
CnCount = 0;
}
DialogResult = DialogResult.OK;
// this.Hide();
}
/// <summary>
/// Text changed, update filter flags accordingly
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Value_TextChanged(object sender, EventArgs e)
{
MobiusDataType mdt = null, mdt2 = null;
string criteriaString = null, criteriaDisplayString = null, criteriaString2 = null, criteriaDisplayString2 = null;
if (InSetup)
{
return;
}
QueryColumn qc = ColInfo.Qc;
qc.SecondaryFilterType = FilterType.BasicCriteria;
qc.SecondaryCriteria = qc.SecondaryCriteriaDisplay = "";
bool validInput = true;
if (Value.Visible && Value.Text.Trim() == "") // no filter if nothing entered
{
qc.SecondaryCriteria = qc.SecondaryCriteriaDisplay = "";
}
else if (CompOp == CompareOp.Like) // keep criteria in literal text form for like
{
qc.SecondaryCriteria = qc.MetaColumn.Name + " like " + Lex.AddSingleQuotes(Value.Text.Trim());
qc.SecondaryCriteriaDisplay = qc.ActiveLabel + " contains " + Lex.AddSingleQuotes(Value.Text.Trim());
}
else // normalize for comparison for other filter types
{
string op = CompareOpString.Values[(int)CompOp];
string opd = CompareOpDisplayString.Values[(int)CompOp];
if (Value.Visible) // get first value
{
if (MobiusDataType.TryParse(qc.MetaColumn.DataType, Value.Text, out mdt))
{
criteriaString = mdt.FormatForCriteria();
criteriaDisplayString = mdt.FormatCriteriaForDisplay();
}
else
{
validInput = false;
}
}
if (CompOp == CompareOp.Between) // get 2nd value
{
if (MobiusDataType.TryParse(qc.MetaColumn.DataType, Value2.Text, out mdt2))
{
criteriaString2 = mdt2.FormatForCriteria();
criteriaDisplayString2 = mdt2.FormatCriteriaForDisplay();
}
else
{
validInput = false;
}
}
if (validInput)
{
qc.SecondaryCriteria = qc.MetaColumn.Name + " " + op;
qc.SecondaryCriteriaDisplay = qc.ActiveLabel + " " + opd;
if (mdt != null)
{
qc.SecondaryCriteria += " " + Lex.AddSingleQuotes(criteriaString);
qc.SecondaryCriteriaDisplay += " " + Lex.AddSingleQuotes(criteriaDisplayString);
}
if (mdt2 != null)
{
qc.SecondaryCriteria += " and " + Lex.AddSingleQuotes(criteriaString2);
qc.SecondaryCriteriaDisplay += " and " + Lex.AddSingleQuotes(criteriaDisplayString2);
}
}
else
{
qc.SecondaryCriteria = qc.MetaColumn.Name + " like 'No-Match-String'"; // assure no matches
qc.SecondaryCriteriaDisplay = qc.ActiveLabel + " " + opd + " invalid value";
}
}
QueryManager.QueryResultsControl.UpdateFiltering(ColInfo); // Update the filters and the associated view
SyncBaseQuerySecondaryCriteria(qc); // sync any base query
return;
}