/// <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); } }
/// <summary> /// Build tooltip for data row /// </summary> /// <param name="dri">Data row index</param> /// <returns></returns> internal SuperToolTip BuildDataRowTooltip( TooltipDimensionDef ttDim, int dri) { ColumnMapMsx cm; QueryTable qt; QueryColumn qc; MetaTable mt; MetaColumn mc; int i1, i2; if (BaseQuery == null || BaseQuery.Tables.Count == 0 || Dtm == null) { return(null); } qt = BaseQuery.Tables[0]; SuperToolTip s = new SuperToolTip(); s.MaxWidth = 200; s.AllowHtmlText = DefaultBoolean.True; ToolTipItem i = new ToolTipItem(); i.AllowHtmlText = DefaultBoolean.True; i.Appearance.TextOptions.WordWrap = WordWrap.Wrap; ColumnMapCollection cml2 = new ColumnMapCollection(); // list of fields we'll actually display int strPos = -1; ColumnMapCollection cml = ttDim.Fields; if (cml.Count == 0) { return(s); } for (i1 = 0; i1 < cml.Count; i1++) { cm = cml[i1]; qc = cm.QueryColumn; if (qc == null || !cm.Selected) { continue; } //if (qc.IsKey) continue; if (qc.MetaColumn.DataType == MetaColumnType.Structure) { strPos = i1; } for (i2 = 0; i2 < cml2.Count; i2++) // see if already have the column { if (qc == cml2[i2].QueryColumn) { break; } } if (i2 < cml2.Count) { continue; } cml2.Add(cm); } if (cml2.Count == 0) { return(null); // no fields } if (strPos < 0 && ttDim.IncludeStructure) { // include str if requested & not already included qc = qt.FirstStructureQueryColumn; strPos = cml2.Count; // put str at end //strPos = keyPos + 1; // place str after key if (qc != null && qc.Selected) // regular selected Qc? { cml2.ColumnMapList.Insert(strPos, ColumnMapMsx.BuildFromQueryColumn(qc)); } else // look in root table for a structure { mt = qt.MetaTable.Root; if (mt.FirstStructureMetaColumn != null) { qt = new QueryTable(mt); qc = new QueryColumn(); // add qc with no vo pos as indicator that must be selected qc.MetaColumn = mt.FirstStructureMetaColumn; qc.Selected = true; qt.AddQueryColumn(qc); cml2.ColumnMapList.Insert(strPos, ColumnMapMsx.BuildFromQueryColumn(qc)); } } } string keyVal = ""; foreach (ColumnMapMsx fli0 in cml2.ColumnMapList) // format each field { qc = fli0.QueryColumn; mc = qc.MetaColumn; i.Text += "<b>"; // build label if (!Lex.IsNullOrEmpty(fli0.ParameterName)) { i.Text += fli0.ParameterName; } else { i.Text += fli0.QueryColumn.ActiveLabel; } i.Text += ": </b>"; if (qc.VoPosition >= 0) { int ti = qc.QueryTable.TableIndex; int dri2 = Dtm.AdjustDataRowToCurrentDataForTable(dri, ti, true); DataRowMx dr = Qm.DataTable.Rows[dri2]; if (dr == null) { continue; } ResultsTable rt = Rf.Tables[ti]; object fieldValue = dr[qc.VoPosition]; if (!NullValue.IsNull(fieldValue)) { if (qc.IsKey) { keyVal = fieldValue.ToString(); } MobiusDataType mdt = MobiusDataType.ConvertToMobiusDataType(qc.MetaColumn.DataType, fieldValue); FormattedFieldInfo ffi = Qm.ResultsFormatter.FormatField(qc, mdt); if (mc.DataType != MetaColumnType.Structure) { i.Text += ffi.FormattedText; i.Text += "<br>"; } else { i = ToolTipUtil.AppendBitmapToToolTip(s, i, ffi.FormattedBitmap); } } else { i.Text += "<br>"; // no data } } else if (!Lex.IsNullOrEmpty(keyVal)) // select structure from db (may already have) { MoleculeMx cs = MoleculeUtil.SelectMoleculeForCid(keyVal, qc.MetaColumn.MetaTable); if (cs != null) { int width = ResultsFormatFactory.QcWidthInCharsToDisplayColWidthInMilliinches(mc.Width, ResultsFormat); FormattedFieldInfo ffi = Qm.ResultsFormatter.FormatStructure(cs, new CellStyleMx(), 's', 0, width, -1); i = ToolTipUtil.AppendBitmapToToolTip(s, i, ffi.FormattedBitmap); } } } if (i.Text.Length > 0) { s.Items.Add(i); } if (s.Items.Count == 0) { // show something by default i = new ToolTipItem(); i.Text = "No fields selected"; i.LeftIndent = 6; i.Appearance.TextOptions.WordWrap = WordWrap.Wrap; s.Items.Add(i); } //ToolTipTitleItem ti = new ToolTipTitleItem(); //ti.Text = "Dude"; //s.Items.Add(ti); //ToolTipItem i = new ToolTipItem(); //i.Text = "Subtext that is fairly long longer longest"; //i.LeftIndent = 6; //i.Appearance.TextOptions.WordWrap = WordWrap.Wrap; //s.Items.Add(i); //i = new ToolTipItem(); //Image img = Bitmap.FromFile(@"C:\Mobius_OpenSource\Mobius-3.0\ClientComponents\Resources\Mobius76x76DkBlueBack.bmp"); //i.Image = img; //s.Items.Add(i); //ToolTipSeparatorItem si = new ToolTipSeparatorItem(); return(s); //ChartPanel.ToolTipController.ToolTipLocation = ToolTipLocation.TopCenter; //ChartPanel.ToolTipController.AllowHtmlText = true; //string s = point.SeriesPointID.ToString(); //s = "This <b>SuperToolTip</b> supports <i>HTML formatting</i>"; }