/// <summary>
/// Do initial load of metatables
/// </summary>
public static void BuildFromXmlFile(string rootFile)
{
MetaTable[] mta;
StreamReader sr = null;
int i1;
try
{
string dir = Path.GetDirectoryName(rootFile);
if (!String.IsNullOrEmpty(dir))
{
MetaTableXmlFolder = dir;
}
sr = new StreamReader(rootFile);
XmlTextReader tr = new XmlTextReader(sr);
mta = MetaTable.DeserializeMetaTables(tr, dir, true);
sr.Close();
}
catch (Exception ex)
{
if (sr != null)
{
sr.Close();
}
throw new Exception("Error processing metatable file " + Lex.Dq(rootFile) + ": " + ex.Message, ex);
}
}
/// <summary>
/// Join an arraylist of values into a .Csv format string
/// </summary>
/// <param name="arrayList"></param>
/// <returns></returns>
public static string JoinCsvString(
List <string> arrayList)
{
StringBuilder sb = new StringBuilder();
for (int si = 0; si < arrayList.Count; si++)
{
string s = arrayList[si];
if (s == null)
{
s = "";
}
if (sb.Length > 0)
{
sb.Append(", ");
}
if (s.IndexOf(",") > 0 || s.IndexOf(" ") > 0 ||
s.IndexOf("\'") > 0 || s.IndexOf("\"") > 0)
{
sb.Append(Lex.Dq(s));
}
else
{
sb.Append(s); // keep as is
}
}
return(sb.ToString());
}
private void BrowseQueries_Click(object sender, EventArgs e)
{
string prompt = "Select Query";
UserObject uo = UserObjectOpenDialog.ShowDialog(UserObjectType.Query, prompt, QueryUo);
if (uo == null)
{
return;
}
Alert existingAlert = Alert.GetAlertByQueryId(uo.Id);
if (existingAlert != null) // only 1 alert per query per user is allowed
{
if (Alert.Id <= 0 || // if this is a new alert or
(Alert.Id > 0 && Alert.Id != existingAlert.Id)) // another alert exists for the query
{
MessageBoxMx.Show("You already an existing alert for query " + Lex.Dq(uo.Name),
"Mobius", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
Query q = QbUtil.ReadQuery(uo.Id);
if (!Alert.QueryValidForAlert(q))
{
return;
}
QueryUo = uo;
QueryName.Text = QueryUo.Name;
}
/// <summary>
/// Add field to query
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void SelectedField_Click(object sender, System.EventArgs e)
{
ToolStripMenuItem mi = (ToolStripMenuItem)sender;
string txt = Lex.Dq(mi.Tag + "." + mi.Text);
txt = PadText(txt);
Criteria.SelectedText = txt;
Criteria.SelectionFont = Criteria.Font;
Criteria.Focus();
}
/// <summary>
/// Check to see that columns are sortable
/// </summary>
/// <param name="sortColumns"></param>
public void ValidateSortColumns(
List <SortColumn> sortColumns)
{
for (int sci = 0; sci < sortColumns.Count; sci++)
{ // be sure all columns are sortable
QueryColumn qc = sortColumns[sci].QueryColumn;
MetaColumnType t = qc.MetaColumn.DataType;
if (t == MetaColumnType.Image || t == MetaColumnType.Structure)
{
throw new Exception("Can't sort on column " + Lex.Dq(qc.MetaColumn.Label) +
" because it is of type " + t.ToString());
}
}
return;
}
/// <summary>
/// Do trusted user authentication
/// </summary>
/// <returns></returns>
public static UserInfo AuthenticateUserTrusted()
{
throw new NotImplementedException();
#if false
UserInfo uInf = new UserInfo();
string publicKey, privateKey;
XlEncrypt.GetKeys(out publicKey, out privateKey);
string encryptedName = Client.Get("Mobius.Client.UIMisc.GetEncryptedDomainUsername(" +
Lex.Dq(publicKey) + ")");
string decrypted = XlEncrypt.Decrypt(encryptedName, privateKey);
string [] sa = decrypted.Split('\\');
uInf.UserDomainName = sa[0].ToUpper();
uInf.UserName = sa[1].ToUpper();
Security.SetUser(uInf.UserName);
return(uInf);
#endif
}
/// <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>
/// Do some combination of copy/cut/delete on a single userObject
/// </summary>
/// <param name="command"></param>
/// <param name="uoArray"></param>
/// <param name="ctc"></param>
/// <param name="copy"></param>
/// <param name="delete"></param>
//private static void CopyCutDelete(
// string command,
// UserObject uo,
// ContentsTreeControl ctc,
// bool copy,
// bool delete)
//{
// UserObject[] uoArray = new UserObject[1] { uo };
// CopyCutDelete(command, uoArray, ctc, copy, delete);
//}
/// <summary>
/// Do some combination of copy/cut/delete on an array of Userobjects
/// </summary>
/// <param name="command"></param>
/// <param name="uoArray"></param>
/// <param name="ctc"></param>
/// <param name="copy"></param>
/// <param name="delete"></param>
static void CopyCutDelete(
string command,
UserObject[] uoArray,
ContentsTreeControl ctc,
bool copy,
bool delete)
{
// Copy to clipboard
if (copy)
{
if (!UserHasReadAccess(SS.I.UserName, uoArray))
{
MessageBoxMx.ShowError("You are not authorized to copy these nodes. Copy canceled.");
return;
}
if (!UserObject.CanCopyPaste(uoArray))
{
MessageBoxMx.ShowError("Can't copy/cut user objects of these types. Copy/cut canceled.");
return;
}
List <UserObject> userObjectArray = new List <UserObject>();
foreach (UserObject userObject in uoArray)
{
UserObject uo = UserObjectDao.Read(userObject.Id);
userObjectArray.Add(uo);
}
string serializedUos = Serialize(userObjectArray.ToArray());
if (serializedUos != null)
{
Clipboard.SetText(serializedUos);
}
}
// Delete object
if (delete)
{
if (!UserHasWriteAccess(SS.I.UserName, uoArray))
{
MessageBoxMx.ShowError("You are not authorized to cut/delete these nodes.");
return;
}
//for now, don't allow folders to be deleted if they aren't empty
foreach (var uo in uoArray)
{
if (uo.Type == UserObjectType.Folder)
{
MetaTreeNode folderNode = UserObjectTree.BuildNode(uo);
if (UserObjectTree.FolderNodes.ContainsKey(folderNode.Target.ToUpper()))
{
folderNode = UserObjectTree.FolderNodes[folderNode.Target.ToUpper()];
if (folderNode.Nodes.Count > 0)
{
MessageBoxMx.ShowError("Folder \"" + folderNode.Label + "\" is not empty and cannot be deleted.");
return;
}
}
}
}
if (!copy)
{
string caption = "Confirm " + UserObject.GetTypeLabel(uoArray[0].Type) + " Delete";
string txt = "Are you sure you want to delete " + Lex.Dq(uoArray[0].Name);
if (uoArray.Length > 1)
{
txt = "Are you sure you want to delete these " + uoArray.Length + " items?";
}
DialogResult dr = MessageBoxMx.Show(txt, caption, MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr != DialogResult.Yes)
{
return;
}
foreach (var uo in uoArray)
{
UserObject userObject = null;
userObject = UserData.IsUserDataObject(uo) ? UserObjectDao.Read(uo.Id) : uo;
bool succeeded = userObject != null && UserObjectDao.Delete(userObject.Id);
// remove userobject from database
if (UserData.IsUserDataObject(userObject)) // start removing any associated user data
{
UserData.StartBackgroundDataDelete(userObject);
}
}
//if (UserData.IsUserDataObject(uo)) uo = UserObjectDao.Read(uo.Id); // will need content to del assoc user data
//bool succeeded = UserObjectDao.Delete(uo.Id); // remove userobject from database
//if (UserData.IsUserDataObject(uo)) // start removing any associated user data
// UserData.StartBackgroundDataDelete(uo);
}
else
{
foreach (var uo in uoArray)
{
UserObjectDao.Delete(uo.Id); // just remove userobject from database, will leave orphan data for annotation tables but need for paste
}
}
if (ctc != null) // redraw the subtree without the deleted user object
{
UserObjectTree.RefreshSubtreeInTreeControl(uoArray[0], ctc);
}
}
}
/// <summary>
/// Command entry to execute a query in the background and save the results for later retrieval
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static string RunBackgroundQuery(
string args)
{
UserObject uo = null;
bool sendEmail = false;
string emailSubject = null;
string msg;
int queryId = -1;
string[] sa = args.Split(' ');
if (sa.Length > 0)
{
if (int.TryParse(sa[0].Trim(), out queryId))
{
uo = UserObjectDao.Read(queryId);
}
}
if (uo == null)
{
emailSubject = UmlautMobius.String + " background query results error";
msg = "RunQueryInBackground failed to read query " + queryId;
ServicesLog.Message(msg);
Email.Send(
null, SS.I.UserInfo.EmailAddress, emailSubject, msg);
return(msg);
}
if (sa.Length > 1)
{
bool.TryParse(sa[1].Trim(), out sendEmail);
}
Query q = Query.Deserialize(uo.Content);
q.UserObject = uo; // copy user object to get current name, etc.
q.IncludeRootTableAsNeeded();
if (sendEmail)
{
emailSubject = UmlautMobius.String + " background query results for " + Lex.Dq(q.UserObject.Name);
}
return(RunBackgroundQuery(q, emailSubject, "MobiusBackgroundQueryEmailTemplate.htm"));
}
/// <summary>
/// This method runs in a background process and exports data to the specified destination
/// </summary>
/// <param name="objectIdString">Id of UserObject containing run parameters in a serialized ResultsFormat</param>
/// <param name="templateFileName">Name of template file to use</param>
/// <param name="emailResultsHtml">If true then send email otherwise just return html</param>
/// <returns></returns>
public static string RunBackgroundExport(
string objectIdString,
string templateFileName,
bool emailResultsHtml,
out bool copiedToDestinationFile,
int alertId = 0)
{
ResultsFormat rf;
Query q;
string msg = "";
int objId;
//if (ClientState.IsDeveloper)
//{
// ServicesLog.Message(SS.I.UserName + ": BackgrounExport Debug");
// //DataTableManager.AllowCaching = false;
// DataTableManager.DebugBasics = true;
// DataTableManager.DebugCaching = true;
//}
if (String.IsNullOrEmpty(templateFileName))
{
templateFileName = "MobiusBackgroundExportEmailTemplate.htm";
}
ServicesLog.Message("RunBackgroundExport started: UserObject id = " + objectIdString);
string emailSubject = UmlautMobius.String + " background export results";
copiedToDestinationFile = false;
try
{
if (!int.TryParse(objectIdString, out objId))
{
throw new Exception("Invalid UserObjectId");
}
UserObject uo = UserObjectDao.Read(objId);
if (uo == null)
{
throw new Exception("UserObject not found");
}
QueryManager qm = new QueryManager();
rf = ResultsFormat.Deserialize(uo.Content);
if (rf == null)
{
throw new Exception("Failed to deserialize ResultsFormat");
}
string clientFile = rf.OutputFileName; // ultimate file we want to go to
rf.OutputFileName = GetServerFileName(rf, objId); // get file name to export to on server & use here temporarily
qm.ResultsFormat = rf;
rf.QueryManager = qm;
q = QbUtil.ReadQuery(rf.QueryId);
if (q == null)
{
throw new Exception("Failed to read query: " + rf.QueryId);
}
q.IncludeRootTableAsNeeded();
qm.Query = q;
q.QueryManager = qm;
emailSubject += " for query " + Lex.Dq(q.UserObject.Name); // include query name in subject
ResultsFormatFactory rff = new ResultsFormatFactory(rf);
rff.Build();
ResultsFormatter fmtr = new ResultsFormatter(qm);
DataTableManager dtm = new DataTableManager(qm);
dtm.BeginCaching(); // allow caching of DataTable
dtm.PurgeDataTableWithoutWritingToCacheFile = true; // skip actual writing of cache since it won't be read back in
qm.DataTableManager = dtm;
qm.DataTable = DataTableManager.BuildDataTable(rf.Query); // build data table to receive data
QueryExec qex = new QueryExec(rf);
msg = qex.RunQuery3(rf, false, false); // do the export
int compoundCount = 0;
int rowCount = 0;
QueryEngine qe = qex.QueryEngine;
if (qe != null)
{
compoundCount = qm.DataTableManager.KeyCount;
rowCount = qm.DataTableManager.TotalRowsTransferredToDataTable; // use this for accurate row count
}
dtm.EndCaching(); // close cache file (note: resets key/row counts)
if (compoundCount <= 0 || rowCount <= 0) // any results
{
msg = "Query " + Lex.Dq(q.UserObject.Name) + " returned no results.";
Email.Send(
null, SS.I.UserInfo.EmailAddress, emailSubject, msg);
return(msg);
}
if (ServerFile.CanWriteFileFromServiceAccount(clientFile))
{ // copy to dest file if possible
try
{
FileUtil.CopyFile(rf.OutputFileName, clientFile);
copiedToDestinationFile = true;
rf.OutputFileName = clientFile;
}
catch (Exception ex)
{
ServicesLog.Message("Error copying file from service account: " + clientFile + "\n" + DebugLog.FormatExceptionMessage(ex));
}
}
string viewCmd = "Retrieve Background Export " + uo.Id;
msg = "RunBackgroundExport ended: UserObjectId = " + objectIdString;
if (emailResultsHtml)
{
MailBackgroundExportResults(
q,
clientFile,
rowCount,
compoundCount,
copiedToDestinationFile,
viewCmd,
SS.I.UserInfo.EmailAddress,
emailSubject,
templateFileName);
ServicesLog.Message(msg);
return(msg);
}
else // just fill in values & return
{
string html = ReadTemplateFile(templateFileName);
html = SubstituteBackgroundExportParameters(
html, "", rf.OutputFileName, rowCount, compoundCount, copiedToDestinationFile, viewCmd);
ServicesLog.Message(msg);
return(html);
}
}
catch (Exception ex)
{
if (alertId > 0)
{
msg += "Alert: " + alertId + " ";
}
msg +=
"RunBackgroundExport exception: BackgroundExportId = " + objectIdString + ",\r\n" +
DebugLog.FormatExceptionMessage(ex);
Email.Send(
null, SS.I.UserInfo.EmailAddress, emailSubject, msg);
ServicesLog.Message(msg);
return(msg);
}
}
/// <summary>
/// Method to run query in background and save the results for later retrieval
/// </summary>
/// <param name="q"></param>
/// <param name="emailSubject">Send email if defined</param>
/// <param name="templateName"></param>
/// <returns></returns>
public static string RunBackgroundQuery(
Query q,
string emailSubject,
string templateName)
{
ResultsFormat rf;
QueryManager qm;
DataTableManager dtm;
string msg = "", html = "", resultsFileName;
string viewCmd = "View Background Query Results";
bool notifyUserByEmail = !Lex.IsNullOrEmpty(emailSubject);
try // execute the query & read in all results
{
QbUtil.AddQueryAndRender(q, false); // add it to the query builder
q.BrowseSavedResultsUponOpen = false; // be sure query is run rather than using existing results
msg = QueryExec.RunQuery(q, OutputDest.WinForms);
qm = q.QueryManager as QueryManager;
dtm = qm.DataTableManager;
DialogResult dr = dtm.CompleteRetrieval();
}
catch (Exception ex) // some exceptions are normal, e.g. no criteria, others may be bugs
{
msg = "RunQueryInBackground could not complete due to an unexpected exception: " + DebugLog.FormatExceptionMessage(ex);
ServicesLog.Message(msg);
if (notifyUserByEmail)
{
Email.Send(null, SS.I.UserInfo.EmailAddress, emailSubject, msg);
}
return(msg);
}
if (dtm.KeyCount == 0)
{
msg = "Query " + Lex.Dq(q.UserObject.Name) + " returned no results.";
if (notifyUserByEmail)
{
Email.Send(null, SS.I.UserInfo.EmailAddress, emailSubject, msg);
}
return(msg);
}
try
{
resultsFileName = q.ResultsDataTableFileName; // see if name supplied in query
if (Lex.IsNullOrEmpty(resultsFileName))
{
resultsFileName = "Query_" + q.UserObject.Id + "_Results.bin";
}
resultsFileName = ServicesIniFile.Read("BackgroundExportDirectory") + @"\" + resultsFileName;
dtm.WriteBinaryResultsFile(resultsFileName); // write the file
UserObject cidListUo = SaveBackgroundQueryResultsReferenceObject(qm, "BkgrndQry", resultsFileName);
if (!Lex.IsNullOrEmpty(templateName))
{
html = ReadTemplateFile(templateName);
}
if (notifyUserByEmail)
{
AlertUtil.MailResultsAvailableMessage( // send the mail
q,
dtm.KeyCount,
SS.I.UserInfo.EmailAddress,
emailSubject,
viewCmd,
cidListUo.Id,
null,
html);
}
else
{
html = SubstituteBackgroundExportParameters(html, "", "", dtm.RowCount, dtm.KeyCount, false, "");
return(html); // return the html
}
}
catch (Exception ex)
{
msg = "Error sending background query results: " + DebugLog.FormatExceptionMessage(ex);
ServicesLog.Message(msg);
}
return(msg);
}
/// <summary>
/// Search button clicked, process input
/// </summary>
/// <returns></returns>
DialogResult ProcessInput()
{
CidList cidList = null;
StreamReader structureFileReader = null;
string qid; // query identifier, compoundId, file name or sdFile key value
QueryManager qm;
DataTableMx dt;
DataColumn dc;
DataRowMx dr;
//object[] dr; // if using Qe
DialogResult dlgRslt = DialogResult.OK;
Query q = null;
QueryTable qt = null;
QueryColumn simScoreQc, structQc; // query column containing latest query settings
MetaTable mt = null;
MetaColumn keyMc = null, structMc = null, dbSetMc = null, simScoreMc = null, mc;
MetaColumnType storageType;
string txt, tok;
if (DatabasesToSearch.Text == "")
{
MessageBoxMx.ShowError("Databases to search must be defined.");
DatabasesToSearch.Focus();
return(DialogResult.Cancel);
}
// Get list of databases
string[] dba = DatabasesToSearch.Text.Split(',');
List <MetaTable> tables = new List <MetaTable>();
foreach (string dbLabel0 in dba)
{
string dbLabel = dbLabel0.Trim();
RootTable dbInfo = RootTable.GetFromTableLabel(dbLabel);
if (dbInfo == null)
{
MessageBoxMx.ShowError("Can't find database " + DatabasesToSearch.Text);
DatabasesToSearch.Focus();
return(DialogResult.Cancel);
}
mt = MetaTableCollection.Get(dbInfo.MetaTableName);
if (mt == null)
{
MessageBoxMx.ShowError("Unable to locate parent structure table for database: " + DatabasesToSearch.Text);
DatabasesToSearch.Focus();
return(DialogResult.Cancel);
}
if (dbSetMc == null)
{
dbSetMc = mt.DatabaseListMetaColumn;
}
tables.Add(mt);
}
if (dbSetMc == null)
{
throw new Exception("\"Databases\" metacolumn not found for any of the databases to search");
}
// Validate other form values
RetrieveStructures = RetrieveMatchingStructures.Checked;
bool fromList = FromList.Checked;
int listCidsRead = 0;
int inputQueryCount = -1;
if (fromList) // using list, validate list name
{
if (SavedListUo == null)
{
MessageBoxMx.ShowError("Compound list must be defined.");
ListName.Focus();
return(DialogResult.Cancel);
}
cidList = CidListCommand.Read(SavedListUo);
if (cidList == null)
{
MessageBoxMx.ShowError("Error reading list.");
ListName.Focus();
return(DialogResult.Cancel);
}
inputQueryCount = cidList.Count;
}
else // Using SdFile, validate SdFile name
{
StructureFile = FileName.Text;
if (StructureFile == "")
{
MessageBoxMx.ShowError("File must be defined.");
FileName.Focus();
return(DialogResult.Cancel);
}
try { structureFileReader = new StreamReader(StructureFile); structureFileReader.Close(); }
catch (Exception ex)
{
MessageBoxMx.ShowError("Can't read file: " + Lex.Dq(StructureFile));
FileName.Focus();
return(DialogResult.Cancel);
}
keyField = KeyField.Text; // get key, blank to use name in 1st line
inputQueryCount = -1; // don't know how many queries unless we read the file (todo?)
}
tok = ResultsName.Text.Trim(); // name to store results under
if (tok == "")
{
MessageBoxMx.ShowError("A name for the results must be provided.");
ResultsName.Focus();
return(DialogResult.Cancel);
}
if (SubStruct.Checked)
{
Psc.SearchType = StructureSearchType.Substructure;
}
else if (Full.Checked)
{
Psc.SearchType = StructureSearchType.FullStructure;
}
else if (Similarity.Checked)
{
Psc.SearchType = StructureSearchType.MolSim;
}
else
{
throw new Exception("Unrecognized search type");
}
// Write initial log entries
SearchCount++;
string logFileName = ClientDirs.DefaultMobiusUserDocumentsFolder + @"\Multistructure Search " + SearchCount + ".txt";
if (!UIMisc.CanWriteFileToDefaultDir(logFileName))
{
return(DialogResult.Cancel);
}
LogStream = new StreamWriter(logFileName);
if (ResultsUo == null)
{
ResultsUo = new UserObject(UserObjectType.Annotation);
}
ResultsUo.Name = tok;
UserObjectTree.GetValidUserObjectTypeFolder(ResultsUo);
DateTime startTime = DateTime.Now;
WriteToLog("Multiple " + Psc.SearchType + " Search");
WriteToLog("Databases: " + DatabasesToSearch.Text);
WriteToLog("Date: " + startTime);
if (fromList)
{
WriteToLog("Input List: " + SavedListUo.Name);
}
else
{
WriteToLog("Input Structure File: " + StructureFile);
}
WriteToLog("Output List: " + ResultsUo.Name);
WriteToLog("Log File: " + logFileName);
WriteToLog("");
WriteToLog("Query, Match, Score");
int queryCount = 0;
int matchAtLeastOneCount = 0;
MoleculeMx queryStructure = null; // current structure being searched
CidList matchList = new CidList();
List <MatchData> matchData = new List <MatchData>();
if (FromFile.Checked) // open SdFile as required
{
structureFileReader = new StreamReader(StructureFile);
}
// Search of structures one at a time
while (true)
{
if (fromList) // get next structure from list
{
if (listCidsRead >= cidList.Count)
{
break;
}
qid = cidList[listCidsRead].Cid;
listCidsRead++;
if (qid.Trim() == "")
{
continue;
}
if (qid.ToLower().IndexOf(".mol") > 0 || qid.ToLower().IndexOf(".skc") > 0)
{ // file reference
if (!File.Exists(qid))
{
continue;
}
if (qid.ToLower().IndexOf(".mol") > 0)
{
queryStructure = MoleculeMx.ReadMolfile(qid);
}
else
{
queryStructure = MoleculeMx.ReadSketchFile(qid);
}
}
else
{
queryStructure = MoleculeUtil.SelectMoleculeForCid(qid);
}
if (queryStructure == null || queryStructure.AtomCount == 0)
{
continue; // oops
}
}
else // get next structure from input file
{
qid = null;
if (StructureFile.ToLower().EndsWith(".sdf"))
{
List <SdFileField> fList = SdFileDao.Read(structureFileReader);
if (fList == null) // end of sdFile
{
structureFileReader.Close();
break;
}
if (fList.Count == 0)
{
continue;
}
queryStructure = new MoleculeMx(MoleculeFormat.Molfile, fList[0].Data);
if (queryStructure == null || queryStructure.AtomCount == 0)
{
continue;
}
if (keyField != "") // key field specified?
{
qid = SdFileDao.GetDataField(fList, keyField);
}
else // get name from 1st line of molfile
{
string molFile = fList[0].Data;
int i1 = molFile.IndexOf("\n");
if (i1 == 0)
{
qid = "";
}
else
{
qid = molFile.Substring(0, i1).Trim();
}
}
if (string.IsNullOrEmpty(qid))
{
qid = SdFileDao.GetDataField(fList, "compound_id");
}
}
else // assume smiles file
{
string smiles = structureFileReader.ReadLine();
if (smiles == null) // end of sdFile
{
structureFileReader.Close();
break;
}
smiles = smiles.Trim();
if (smiles.Length == 0)
{
continue;
}
int i1 = smiles.IndexOf(","); // get any preceeding queryId
if (i1 < 0)
{
i1 = smiles.IndexOf("\t");
}
if (i1 >= 0)
{
qid = smiles.Substring(0, i1).Trim();
smiles = smiles.Substring(i1 + 1).Trim();
}
queryStructure = new MoleculeMx(MoleculeFormat.Smiles, smiles);
if (queryStructure == null || queryStructure.AtomCount == 0)
{
continue;
}
}
if (qid == null || qid.Trim() == "")
{
qid = (queryCount + 1).ToString(); // be sure we have a query id
}
}
queryCount++; // count the query
if (queryStructure == null || queryStructure.AtomCount == 0)
{
WriteToLog("Error converting specific structure " + queryCount.ToString() + ", " + qid);
continue;
}
queryStructure.RemoveStructureCaption(); // remove any Mobius-added caption
Psc.Molecule = queryStructure;
string msg =
"Searching Structure: " + queryCount.ToString();
if (inputQueryCount > 0)
{
msg += " of " + inputQueryCount.ToString();
}
msg += "\n" +
"Structures with one or more matches: " + matchAtLeastOneCount.ToString() + "\n" +
"Total Matches: " + matchList.Count.ToString();
Progress.Show(msg);
// Do the search over the list of databases
for (int ti = 0; ti < tables.Count; ti++)
{
mt = tables[ti];
q = new Query(); // build basic query
//q.SingleStepExecution = true; // do in single step (doesn't currently return sim score)
q.ShowStereoComments = false;
qt = new QueryTable(mt);
q.AddQueryTable(qt);
qt.SelectKeyOnly(); // start selecting desired cols
keyMc = mt.KeyMetaColumn;
structMc = mt.FirstStructureMetaColumn;
structQc = qt.GetQueryColumnByName(structMc.Name);
structQc.Selected = RetrieveStructures;
dbSetMc = mt.DatabaseListMetaColumn;
if (dbSetMc == null)
{
throw new Exception("\"Databases\" metacolumn not found for table: " + mt.Label);
}
QueryColumn dbSetQc = qt.GetQueryColumnByName(dbSetMc.Name);
dbSetQc.Selected = true; // select the database name
RootTable root = RootTable.GetFromTableName(mt.Name);
txt = " in (" + root.Label + ")";
dbSetQc.Criteria = dbSetMc.Name + txt;
dbSetQc.CriteriaDisplay = txt;
simScoreMc = mt.SimilarityScoreMetaColumn;
simScoreQc = null;
if (simScoreMc != null) // get sim score if it exists
{
simScoreQc = qt.GetQueryColumnByName(simScoreMc.Name);
simScoreQc.Selected = true; // return sim score
}
Psc.QueryColumn = structQc;
ParsedStructureCriteria psc2 = AdjustSearchForSmallWorldAsNeeded(Psc);
psc2.ConvertToQueryColumnCriteria(structQc); // format the QC for the structure search
DateTime t0 = DateTime.Now;
//QueryEngine qe = new QueryEngine();
//qe.NextRowsMin = 1000; // minimum number of rows to prefetch
//qe.NextRowsMax = -1; // maximum number of rows to prefetch
//qe.NextRowsMaxTime = 10000; // max time in milliseconds for next fetch
//qe.ExecuteQuery(q);
qm = new QueryManager();
try { dlgRslt = qm.ExecuteQuery(ref q); }
catch (Exception ex)
{
WriteToLog("Error searching structure: " + ex.Message + ", " + queryCount.ToString() + ", " + qid);
continue;
}
if (dlgRslt != DialogResult.OK)
{
return(dlgRslt);
}
double executeTime = TimeOfDay.Delta(ref t0);
int offset = qm.DataTableManager.KeyValueVoPos + 1;
//int offset = 0; // for QE
int keyPos = offset++;
int strPos = RetrieveStructures ? offset++ : -1;
int dbPos = offset++;
int simPos = offset++;
int fetchCnt = 0;
while (true)
{
dr = qm.NextRow();
//dr = qe.NextRow(); // for Qe
if (dr == null)
{
break;
}
fetchCnt++;
if (fetchCnt == 1)
{
matchAtLeastOneCount++; // number of queries that have at least one match
}
MatchData md = new MatchData();
md.Qno = queryCount;
md.Qid = qid;
if (RetrieveStructures)
{
md.Qstr = "Chime=" + queryStructure.GetChimeString();
}
CompoundId cid = CompoundId.ConvertTo(dr[keyPos]);
md.Mid = cid.Value;
if (RetrieveStructures)
{
MoleculeMx ms = MoleculeMx.ConvertTo(dr[strPos]);
if (!NullValue.IsNull(ms))
{
md.Mstr = "Chime=" + ms.GetChimeString();
}
}
StringMx db = StringMx.ConvertTo(dr[dbPos]);
if (!NullValue.IsNull(db))
{
md.Db = db.Value;
}
if (Psc.SearchType == StructureSearchType.MolSim)
{
NumberMx nex = NumberMx.ConvertTo(dr[simPos]);
if (!NullValue.IsNull(nex))
{
md.Score = nex.Value;
}
}
if (matchList.Contains(cid.Value)) // already have compound id as match for other query?
{
if (Psc.SearchType != StructureSearchType.MolSim)
{
continue; // if similarity search see if more similar
}
CidListElement le = matchList.Get(cid.Value); // reference current score
if (le.Tag > md.Score)
{
continue; // only replace if more similar
}
matchList.Remove(le.Cid); // remove from list
for (int mi = 0; mi < matchData.Count; mi++) // remove from data
{
if (matchData[mi].Mid == md.Mid)
{
matchData.RemoveAt(mi);
break;
}
}
}
matchList.Add(md.Mid);
matchList.Get(md.Mid).Tag = md.Score; // keep score in list
matchData.Add(md); // add to results
txt = md.Qid + ", " + md.Mid + ", " + md.Score.ToString();
WriteToLog(txt);
} // Fetch result loop
double fetchTime = TimeOfDay.Delta(ref t0);
} // DB loop
if (Progress.CancelRequested)
{
Progress.Hide();
MessageBoxMx.ShowError("Search cancelled.");
try { LogStream.Close(); } catch { }
return(DialogResult.Cancel);
}
} // key loop
CidListCommand.WriteCurrentList(matchList); // write the list of numbers
UsageDao.LogEvent("MultipleStructSearch");
txt =
"=== Multiple structure search complete ===\r\n\r\n" +
"Structures Searched: " + queryCount.ToString() + "\r\n";
txt +=
"Structures with one or more matches: " + matchAtLeastOneCount.ToString() + "\r\n" +
"Total Matches: " + matchList.Count.ToString() + "\r\n";
TimeSpan ts = DateTime.Now.Subtract(startTime);
ts = new TimeSpan(ts.Hours, ts.Minutes, ts.Seconds);
txt += "Total Time: " + ts + "\r\n\r\n";
WriteToLog("\r\n" + txt);
try { LogStream.Close(); } catch { }
if (matchList.Count == 0)
{
Progress.Hide();
MessageBoxMx.ShowError("No matches have been found.");
return(DialogResult.Cancel);
}
tok = "Matching compound ids";
if (Psc.SearchType == StructureSearchType.MolSim)
{
tok = "Similar compound ids";
}
txt += tok + " have been written to the current list: " + ResultsUo.Name + "\n" +
"Log file written to: " + logFileName + "\n\n" +
"Do you want to view the match results?";
DialogResult dRslt = MessageBoxMx.Show(txt, "Multiple Structure Search", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
if (dRslt == DialogResult.Cancel)
{
return(DialogResult.Cancel);
}
else if (dRslt == DialogResult.No) // show log
{
SystemUtil.StartProcess(logFileName);
return(DialogResult.Cancel);
}
// Display results
Progress.Show("Formatting results...");
mt = new MetaTable();
mt.Name = "MULTSTRUCTSEARCH_" + SearchCount;
mt.Label = "Multiple Structure Search " + SearchCount;
mt.TableMap = "Mobius.Tools.MultStructSearch"; // plugin id
mt.MetaBrokerType = MetaBrokerType.NoSql;
ColumnSelectionEnum structureColumnSelection = RetrieveStructures ? ColumnSelectionEnum.Selected : ColumnSelectionEnum.Unselected;
keyMc = keyMc.Clone();
keyMc.Name = "MatchingCid";
keyMc.Label = "Matching Compound Id";
mt.AddMetaColumn(keyMc);
structMc = structMc.Clone();
structMc.Name = "MatchingStructure";
structMc.Label = "Matching Structure";
structMc.InitialSelection = structureColumnSelection;
mt.AddMetaColumn(structMc);
dbSetMc = dbSetMc.Clone();
dbSetMc.Name = "Database";
mt.AddMetaColumn(dbSetMc);
//if (DatabasesToSearch.Text.Contains(","))
dbSetMc.InitialSelection = ColumnSelectionEnum.Selected;
mc = mt.AddMetaColumn("Molsimilarity", "Similarity Search Score", MetaColumnType.Number, ColumnSelectionEnum.Unselected, 10);
if (Psc.SearchType == StructureSearchType.MolSim)
{
mc.InitialSelection = ColumnSelectionEnum.Selected;
}
mc = mt.AddMetaColumn("QueryNo", "Query Number", MetaColumnType.Integer);
//mc = mt.AddMetaColumn("QueryMatchNo", "Query Match Number", MetaColumnType.Integer);
mc = mt.AddMetaColumn("QueryId", "Query Id", MetaColumnType.String);
mc = mt.AddMetaColumn("QueryStructure", "Query Structure", MetaColumnType.Structure);
mc.InitialSelection = structureColumnSelection;
q = ToolHelper.InitEmbeddedDataToolQuery(mt);
dt = q.ResultsDataTable as DataTableMx;
for (int mi = 0; mi < matchData.Count; mi++)
{
MatchData md = matchData[mi];
dr = dt.NewRow();
dr[qt.Alias + ".MatchingCid"] = new CompoundId(md.Mid);
if (RetrieveStructures)
{
dr[qt.Alias + ".MatchingStructure"] = new MoleculeMx(MoleculeFormat.Chime, md.Mstr);
}
dr[qt.Alias + ".Database"] = new StringMx(md.Db);
if (Psc.SearchType == StructureSearchType.MolSim)
{
dr[qt.Alias + ".Molsimilarity"] = new NumberMx(md.Score);
}
dr[qt.Alias + ".QueryNo"] = new NumberMx(md.Qno);
dr[qt.Alias + ".QueryId"] = new StringMx(md.Qid);
if (RetrieveStructures)
{
dr[qt.Alias + ".QueryStructure"] = new MoleculeMx(MoleculeFormat.Chime, md.Qstr);
}
dt.Rows.Add(dr);
}
ToolHelper.DisplayData(q, dt, true);
Progress.Hide();
return(DialogResult.OK);
}
/// <summary>
/// Format qualified number
/// </summary>
/// <param name="qn"></param>
/// <param name="qc"></param>
/// <param name="mergedField"></param>
/// <param name="displayFormat"></param>
/// <param name="decimals"></param>
/// <param name="qnFormat"></param>
/// <param name="outputDest"></param>
/// <returns></returns>
public string Format(
QueryColumn qc,
bool mergedField,
ColumnFormatEnum displayFormat,
int decimals,
QnfEnum qnFormat,
OutputDest outputDest)
{
string result, uri, href, txt, tok;
result = "";
QualifiedNumber qn = this;
MetaColumn mc = qc.MetaColumn;
if (qn.Qualifier == null)
{
qn.Qualifier = "";
}
if (qn.TextValue == null)
{
qn.TextValue = "";
}
if (qn.Hyperlink == null)
{
qn.Hyperlink = "";
}
if (QnSubcolumns.IsCombinedFormat(qnFormat)) // normal combined number
{
if (qn.NumberValue == NullValue.NullNumber && qn.NValue == NullValue.NullNumber &&
qn.NValueTested == NullValue.NullNumber && qn.TextValue == "")
{ // null value
result = NullValueString;
if (outputDest == OutputDest.Html && (NullValueString == "" || NullValueString == " "))
{
result = "<br>"; // html blank
}
return(result);
}
result = qn.Format(displayFormat, decimals);
if (qn.NValue > 1 || qn.NValueTested > 1 || // format with sd, n
(qn.NValue >= 0 && qn.NValueTested > qn.NValue) || // number tested > n
(qn.NumberValue == NullValue.NullNumber && // also null numbers if some nonzero nvalue
(qn.NValue > 0 || qn.NValueTested > 0)))
{
txt = "";
if (qn.NValue > 1 && (qnFormat & QnfEnum.StdDev) != 0 && // include sd
(qn.StandardDeviation != NullValue.NullNumber ||
qn.StandardError != NullValue.NullNumber))
{ // format standard deviation
if ((qnFormat & QnfEnum.DisplayStdDevLabel) != 0)
{
txt += "sd=";
}
if (qn.StandardDeviation == NullValue.NullNumber) // calc sd from se if don't have
{
qn.StandardDeviation = qn.StandardError * Math.Sqrt(qn.NValue);
}
txt += FormatNumber(qn.StandardDeviation, displayFormat, decimals);
}
if (qn.NValue > 1 && (qnFormat & QnfEnum.StdErr) != 0 && // include se
(qn.StandardError != NullValue.NullNumber ||
qn.StandardDeviation != NullValue.NullNumber))
{ // format standard error
if (txt != "")
{
txt += ", ";
}
if ((qnFormat & QnfEnum.DisplayStdErrLabel) != 0)
{
txt += "se=";
}
txt += FormatNumber(qn.StandardError, displayFormat, decimals);
}
if (qn.NValue != NullValue.NullNumber &&
(qnFormat & QnfEnum.NValue) != 0)
{ // format n value
if (txt != "")
{
txt += ", ";
}
if ((qnFormat & QnfEnum.DisplayNLabel) != 0)
{
txt += "n=";
}
txt += qn.NValue.ToString();
if (qn.NValueTested != NullValue.NullNumber && qn.NValueTested != qn.NValue)
{
txt += "/" + qn.NValueTested.ToString(); // add number tested if different
}
}
if (txt != "")
{
result += " (" + txt + ")"; // build complete string
}
}
if (qn.DbLink != null && qn.DbLink != "" && qn.DbLink != "." && !mergedField && // do we have an associated resultId
(outputDest == OutputDest.WinForms || outputDest == OutputDest.Html))
{
uri = qn.FormatHyperlink(qc);
//if (outputDest == OutputDest.Grid) // store link info in separate field
// qn.Hyperlink = uri;
//else if (outputDest == OutputDest.Html) // build full html tag
//{
if (uri != "")
{
result = result = "<a href=" + Lex.Dq(uri) + ">" + result + "</a>";
}
//}
}
}
// Format one piece of a split qualified number
else if ((qnFormat & QnfEnum.Qualifier) != 0) // qualifier
{
result = qn.Qualifier;
}
else if ((qnFormat & QnfEnum.NumericValue) != 0) // basic number
{
if (qn.NumberValue != NullValue.NullNumber)
{
result = FormatNumber(qn.NumberValue, displayFormat, decimals);
}
else
{
result = null;
}
//else (obsolete, don't output any text values into numeric column, add option for separate column later)
//{
// if (qn.NValue != NullValue.NullNumber)
// result = "ND"; // some NValue, return not determined
// else result = qn.TextValue; // return any text value
//}
}
else if ((qnFormat & QnfEnum.StdDev) != 0) // standard deviation
{
if (qn.StandardDeviation != NullValue.NullNumber)
{
result = FormatNumber(qn.StandardDeviation, displayFormat, decimals);
}
}
else if ((qnFormat & QnfEnum.StdErr) != 0) // standard error
{
if (qn.StandardError != NullValue.NullNumber)
{
result = FormatNumber(qn.StandardError, displayFormat, decimals);
}
}
else if ((qnFormat & QnfEnum.NValue) != 0) // N
{
if (qn.NValue != NullValue.NullNumber)
{
result = qn.NValue.ToString();
}
}
else if ((qnFormat & QnfEnum.NValueTested) != 0) // number tested
{
if (qn.NValueTested != NullValue.NullNumber)
{
result = qn.NValueTested.ToString();
}
}
else if ((qnFormat & QnfEnum.TextValue) != 0) // text value
{
if (!Lex.IsNullOrEmpty(qn.TextValue))
{
result = qn.TextValue;
}
}
return(result);
}
/// <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>
/// Format the string representation of the rule
/// </summary>
/// <param name="rulei"></param>
/// <param name="cf"></param>
/// <param name="mc"></param>
/// <returns></returns>
public string ToString(
MetaColumn mc,
int rulei)
{
string txt;
string value = Value;
string value2 = Value2;
if (mc.DataType == MetaColumnType.Structure)
{ // for structure type use rule name or number in header
if (!String.IsNullOrEmpty(Name))
{
txt = Name;
}
else
{
txt = "Rule";
if (rulei >= 0)
{
txt += " " + (rulei + 1);
}
}
}
else // for other types use the condition in the header
{
if (mc.DataType == MetaColumnType.String)
{
value = Lex.Dq(value);
value2 = Lex.Dq(value2);
}
if (Lex.Eq(Op, "Between") || Lex.Eq(Op, "Between (Inclusive)"))
{
txt = value + " - " + value2;
}
else if (Lex.Eq(Op, "Not Between"))
{
txt = "Not " + value + " - " + value2;
}
else if (Lex.Eq(Op, "Equal to"))
{
txt = "= " + value;
}
else if (Lex.Eq(Op, "Not Equal to"))
{
txt = "Not = " + value;
}
else if (Lex.Eq(Op, "Within the Last"))
{
txt = "Within " + value + " " + value2;
}
else
{
txt = Op + " " + value;
}
}
return(txt);
}