/// <summary>
/// Build unpivoted subquery QueryManager including the DataTableManger,
/// ResultsFormat and ResultsFormatter.
/// Build empty DataTable
/// </summary>
/// <returns></returns>
QueryManager InitializeUnpivotedSubqueryQm()
{
Query q2 = null;
DataTableManager dtm2;
DataTableMx dt2;
ResultsFormat rf2;
ResultsFormatFactory rff;
ResultsFormatter rfmtr;
QueryTable qt;
MetaTable mt;
QueryManager qm2 = new QueryManager(); // build output query here
// Setup unpivoted output query
q2 = new Query(qm2);
mt = MetaTableCollection.GetWithException(MultiDbAssayDataNames.CombinedNonSumTableName);
qt = new QueryTable(q2, mt); // be sure proper cols are selected
dtm2 = new DataTableManager(qm2);
dt2 = DataTableManager.BuildDataTable(qm2);
rff = new ResultsFormatFactory(qm2, OutputDest.WinForms);
rff.Build();
rfmtr = new ResultsFormatter(qm2);
return(qm2);
}
/// <summary>
/// Fill the DataTable for the query from a file
/// </summary>
/// <param name="q"></param>
/// <param name="fileName">basic file name without directory</param>
/// <returns></returns>
public static bool LoadDataTableFromFile(
Query q,
string fileName)
{
string clientFileName;
DateTime clientFileDate;
if (!GetSavedDataTableFile(fileName, out clientFileName, out clientFileDate))
{
return(false);
}
QueryManager qm = new QueryManager();
qm.LinkMember(q);
ResultsFormat rf = new ResultsFormat(qm, OutputDest.WinForms);
ResultsFormatFactory rff = new ResultsFormatFactory(qm, OutputDest.WinForms);
rff.Build(); // build format with vo positions
DataTableManager dtm = new DataTableManager(qm);
DataTableMx dt = BuildDataTable(q);
qm.LinkMember(dt);
try
{
qm.DataTableManager.ReadBinaryResultsFile(clientFileName);
q.ResultsDataTable = dt; // link query to DataTable (redundant?)
}
catch (Exception ex)
{ return(false); }
// Map "old" datatable names based on metatable names to use alias instead
foreach (System.Data.DataColumn dc in dt.Columns)
{
string colName = dc.ColumnName;
int i1 = colName.IndexOf(".");
if (i1 < 0)
{
continue;
}
string tName = colName.Substring(0, i1);
string cName = colName.Substring(i1 + 1);
QueryTable qt = q.GetQueryTableByName(tName);
if (qt != null)
{
dc.ColumnName = qt.Alias + "." + cName;
}
}
// todo - complete adjustment to get match between query and DataTable
return(true);
}
/// <summary>
/// Complete setup & execute query
/// </summary>
/// <param name="q"></param>
/// <param name="keys"></param>
/// <returns></returns>
public DialogResult ExecuteQuery(ref Query q, out List <string> keys)
{
Query modifiedQuery = null;
DialogResult dr;
keys = null;
try
{
dr = QueryExec.ValidateQuery(q);
if (dr == DialogResult.Cancel)
{
return(dr);
}
modifiedQuery = QueryEngine.DoPresearchTransformations(q); // do any presearch transforms
}
catch (Exception ex) // failed a precheck condition
{
throw ex;
}
if (modifiedQuery != null)
{
q = modifiedQuery; // if modified then replace original query with this query
}
Query = q; // link in query
q.QueryManager = this;
DataTableManager dtm = new DataTableManager(this);
ResultsFormatFactory fmtFactory = new ResultsFormatFactory(this, OutputDest.TextFile);
fmtFactory.Build(); // build ResultsFormat
DataTable = DataTableManager.BuildDataTable(q); // build data table to receive data
QueryEngine = new QueryEngine();
keys = QueryEngine.ExecuteQuery(q);
DataTableManager.StartRowRetrieval();
return(DialogResult.OK);
}
/// <summary>
/// Create a QueryManager and associated objects from the specified Query and DataTable
/// </summary>
/// <param name="q"></param>
/// <param name="dt"></param>
/// <returns></returns>
public static QueryManager SetupQueryManager(
Query q,
DataTableMx dt)
{
QueryManager qm = new QueryManager();
qm.LinkMember(q);
qm.LinkMember(dt);
DataTableManager dtm = new DataTableManager(qm);
ResultsFormatFactory rff = new ResultsFormatFactory(qm, OutputDest.WinForms);
rff.Build(); // builds format
ResultsFormatter fmtr = new ResultsFormatter(qm);
qm.DataTableManager.InitializeRowAttributes();
qm.DataTableManager.SetRowRetrievalStateComplete();
return(qm);
}
/// <summary>
/// Complete any missing base QueryManager entries with default values
/// Note: graphics control entries are not initialized
/// </summary>
public void CompleteInitialization(ResultsFormat rf)
{
if (Query == null)
{
throw new Exception("Query not defined");
}
if (rf == null)
{
throw new Exception("ResultsFormat not defined");
}
QueryManager qm = this;
ResultsFormat = rf;
ResultsFormatFactory fmtFactory = new ResultsFormatFactory(qm, rf);
fmtFactory.Build(); // build ResultsFormat
ResultsFormatter fmtr = qm.ResultsFormatter;
if (fmtr == null) // be sure we have a results formatter
{
fmtr = new ResultsFormatter(qm);
}
DataTableManager dtm = DataTableManager;
if (dtm == null) // build DataTableManager and DataTableMx
{
dtm = new DataTableManager(qm);
}
if (qm.DataTable == null || qm.DataTable.Columns.Count == 0)
{
qm.DataTable = DataTableManager.BuildDataTable(qm.Query); // build data table to receive data
}
return;
}
/// <summary>
/// Display the tool data
/// </summary>
/// <param name="qm"></param>
/// <param name="serializeContentInQuery"></param>
public static void DisplayDataOld( // Old version from 1/4/2017
QueryManager qm,
MoleculeGridPanel gridPanel,
bool serializeContentInQuery,
bool fitDataToGridWidth = false)
{
MoleculeGridControl grid = null;
bool displayAsNormalQueryResults = (gridPanel == null); // if grid panel not defined assume display is in normal results panel
bool displayAsPopupGrid = (gridPanel != null && gridPanel.Parent is PopupGrid); // display in popup grid
Query q = qm.Query;
q.SetupQueryPagesAndViews(); // be sure we have a default view page
if (serializeContentInQuery)
{ // no database behind this table so persist within the query
MetaTable mt = q.Tables[0].MetaTable; // assume just one metatable
foreach (MetaColumn mc in mt.MetaColumns) // no criteria allowed
{
mc.IsSearchable = false;
}
q.SerializeMetaTablesWithQuery = true; // if no broker then save the metatable definition
q.SerializeResultsWithQuery = true; // also save results when saving the query
q.BrowseSavedResultsUponOpen = true; // open the results when the query is opened
q.ResultsDataTable = qm.DataTable; // point the query to this results table
}
ResultsFormatFactory rff = new ResultsFormatFactory(qm, OutputDest.WinForms);
rff.Build(); // build format
ResultsFormatter fmtr = new ResultsFormatter(qm); // and formatter
if (displayAsNormalQueryResults)
{
gridPanel = SessionManager.Instance.QueryResultsControl.MoleculeGridPageControl.MoleculeGridPanel;
QbUtil.AddQuery(qm.Query); // add to the list of visible queries
QueriesControl.Instance.CurrentBrowseQuery = qm.Query; // make it current
}
grid = gridPanel.SelectBaseGridViewGrid(qm);
//if (qm.ResultsFormat.UseBandedGridView)
// grid = gridPanel.BandedViewGrid;
//else grid = gridPanel.LayoutViewGrid;
qm.MoleculeGrid = grid;
grid.QueryManager = qm;
DataTableMx dt = qm.DataTable; // save ref to data table
grid.DataSource = null; // clear source for header build
if (fitDataToGridWidth && grid.BGV != null)
{
grid.BGV.OptionsView.ColumnAutoWidth = true; // set view for auto width to fit within view
}
grid.FormatGridHeaders(qm.ResultsFormat);
qm.DataTable = dt; // restore data table
qm.DataTableManager.SetResultsKeysFromDatatable(); // set the results keys
if (displayAsNormalQueryResults)
{
QbUtil.SetMode(QueryMode.Browse); // put into browse mode
}
else if (displayAsPopupGrid)
{
PopupGrid pug = gridPanel.Parent as PopupGrid;
pug.Initialize(qm); // be sure popup is initialized
pug.Show();
}
gridPanel.Visible = true;
grid.Visible = true;
grid.DataSource = qm.DataTable; // set the datasource for the grid to the datatable
RefreshDataDisplay(qm);
return;
}
/// <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>
/// Attempt to read existing results file into the query DataTable
/// </summary>
/// <param name="qm"></param>
public void ReadBinaryResultsFile(string fileName)
{
QueryTable qt;
QueryColumn qc;
BinaryReader br = null;
Stopwatch sw = Stopwatch.StartNew();
try
{
bool saveHandlersEnabled = Qm.DataTable.EnableDataChangedEventHandlers(false); // disable for faster load
bool saveUpdateMaxRowsPerKey = UpdateMaxRowsPerKeyEnabled;
UpdateMaxRowsPerKeyEnabled = false; // disable for faster load
int id = Query.UserObject.Id;
if (id <= 0)
{
throw new Exception("Query not saved");
}
if (DataTableMx == null || DataTableMx.Columns.Count == 0)
{
throw new Exception("DataTable not defined");
}
br = BinaryFile.OpenReader(fileName);
string sq = br.ReadString();
Query q0 = Query.Deserialize(sq); // deserialize the saved query
QueryManager qm0 = new QueryManager();
qm0.LinkMember(q0);
ResultsFormat rf0 = new ResultsFormat(qm0, OutputDest.WinForms);
ResultsFormatFactory rff0 = new ResultsFormatFactory(qm0, OutputDest.WinForms);
rff0.Build(); // build format with vo positions
// The cached query cols should match those of the current query: however,
// we'll create a mapping just in case they don't
int voArrayLen0 = br.ReadInt32(); // cached vo array len
int voArrayLen = DataTableMx.Columns.Count - KeyValueVoPos; // current query vo array len
List <int> q0VoMap = new List <int>(); // vo position in cached query data
List <int> qVoMap = new List <int>(); // vo position in current version of query
q0VoMap.Add(0); // first position is the common key value
qVoMap.Add(0);
foreach (QueryTable qt0 in q0.Tables) // scan each table in cached data
{
foreach (QueryColumn qc0 in qt0.QueryColumns) // and each column
{
if (qc0.VoPosition < 0)
{
continue; // skip if not mapped to the vo in cached data
}
int q0VoPos = qc0.VoPosition - KeyValueVoPos; // where it is in cache
int qvoPos = -1; // where it will go
qt = Query.GetTableByName(qt0.MetaTable.Name);
if (qt != null)
{
qc = qt.GetQueryColumnByName(qc0.MetaColumn.Name);
if (qc != null)
{
qvoPos = qc.VoPosition - KeyValueVoPos;
}
}
q0VoMap.Add(q0VoPos); // where it is in saved data
qVoMap.Add(qvoPos); // where it will go (not including attributes & check cols)
}
}
if (q0VoMap.Count != voArrayLen0)
{
throw new Exception("Cached Vo length doesn't match list of selected columns");
}
DataTableMx.Clear(); // clear the rows
CidList cidList = new CidList();
object[] voa = new object[voArrayLen]; // array to fill
while (!BinaryFile.ReaderEof(br)) // process each row
{
for (int mi = 0; mi < q0VoMap.Count; mi++) // each col
{
object o = VoArray.ReadBinaryItem(br);
if (mi == 0 && o != null) // add to key list if key
{
cidList.Add(o.ToString(), false);
}
if (qVoMap[mi] >= 0) // save in new buf if mapped
{
voa[qVoMap[mi]] = o;
}
}
DataRowMx dr = AddDataRow(voa);
}
br.Close();
Qm.DataTable.EnableDataChangedEventHandlers(saveHandlersEnabled);
UpdateMaxRowsPerKeyEnabled = saveUpdateMaxRowsPerKey;
InitializeRowAttributes(false);
ResultsKeys = cidList.ToStringList(); // include keys in DTM as well
double ms = sw.Elapsed.TotalMilliseconds;
return;
}
catch (Exception ex)
{
if (br != null)
{
br.Close();
}
throw new Exception(ex.Message, ex);
}
}