private void FieldsButton_Click(object sender, EventArgs e) { Query q; QueryTable qt, qt2; string advExprExt; Point p = FieldsButton.PointToScreen(new Point(0, FieldsButton.Height)); FieldSelectorControl.Query = AdvancedEditorPseudoQuery; FieldSelectorControl.MetaColumn = null; // no current selection int p1 = AdvancedExpr.SelectionStart; // save position/selection since may change int p2 = AdvancedExpr.SelectionLength; Mobius.Data.QueryColumn qc = FieldSelectorControl.ShowMenu(p); if (qc == null) { return; } MetaColumn mc = qc.MetaColumn; string tok = '"' + mc.MetaTable.Name + "." + mc.Label + '"'; InsertText(tok, p1, p2); qt2 = AdvancedEditorPseudoQuery.GetQueryTableByName(mc.MetaTable.Name); if (qt2 == null) { AdvancedEditorPseudoQuery.AddQueryTable(qc.QueryTable); } }
/// <summary> /// User has changed the value of a cell /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void FieldGridView_CellValueChanged(object sender, CellValueChangedEventArgs e) { int ri = e.RowHandle; if (ri < 0) { return; } GridColumn gc = e.Column; if (gc == null) { return; } int gci = gc.AbsoluteIndex; PivotGridFieldMx f = PivotGrid.Fields[ri] as PivotGridFieldMx; ResultsField rfld = f.ResultsField as ResultsField; Mobius.Data.QueryColumn qc = rfld.QueryColumn; MetaColumn mc = qc.MetaColumn; if (gc.FieldName == "FieldNameCol") { f.Caption = e.Value.ToString(); } else if (gc.FieldName == "AggRoleCol") { return; // handled in CellValueChanging } else if (gc.FieldName == "AggTypeCol") { return; } //else if (gc.FieldName == "HeaderBinningCol") //{ // return; //} else if (gc.FieldName == "SourceColumnCol" || gc.FieldName == "SourceTableCol") { return; } else { return; // something else } }
//void SetMeasureUnits(params DateTimeMeasureUnit[] units) //{ // object prevUnit = String.IsNullOrEmpty(ChartDataMeasureUnit.SelectedItem.ToString()) ? null : Enum.Parse(typeof(DateTimeMeasureUnit), ChartDataMeasureUnit.SelectedItem.ToString()); // string prevItem = ""; // ChartDataMeasureUnit.Properties.Items.Clear(); // foreach (DateTimeMeasureUnit unit in units) // { // string unitName = Enum.GetName(typeof(DateTimeMeasureUnit), unit); // ChartDataMeasureUnit.Properties.Items.Add(unitName); // if (prevUnit != null && object.Equals(unit, (DateTimeMeasureUnit)prevUnit)) // prevItem = unitName; // } // if (!String.IsNullOrEmpty(prevItem)) // ChartDataMeasureUnit.SelectedItem = prevItem; // else // ChartDataMeasureUnit.SelectedIndex = 0; //} //private void cbChartDataMeasureUnit_SelectedIndexChanged(object sender, EventArgs e) //{ // XYDiagram diagram = null; // (XYDiagram)Chart.Diagram; // DateTimeMeasureUnit unit = (DateTimeMeasureUnit)Enum.Parse(typeof(DateTimeMeasureUnit), ChartDataMeasureUnit.SelectedItem.ToString()); // diagram.AxisX.DateTimeScaleOptions.GridAlignment = (DateTimeGridAlignment)unit; // diagram.AxisX.DateTimeScaleOptions.MeasureUnit = unit; // switch (unit) // { // case DateTimeMeasureUnit.Year: // diagram.AxisX.Label.TextPattern = "{A:yyyy}"; // break; // case DateTimeMeasureUnit.Quarter: // diagram.AxisX.Label.TextPattern = "{A:yyyy}"; // todo: fix // //diagram.AxisX.Label.DateTimeOptions.Format = DateTimeFormat.QuarterAndYear; // break; // case DateTimeMeasureUnit.Month: // diagram.AxisX.Label.TextPattern = "{A:yyyy}"; // todo: fix // //diagram.AxisX.DateTimeOptions.Format = DateTimeFormat.MonthAndYear; // break; // default: // break; // } //} /// <summary> /// Field grid cell clicked /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void FieldGrid_MouseClick(object sender, MouseEventArgs e) { GridHitInfo hi = FieldGridView.CalcHitInfo(e.Location); int ri = hi.RowHandle; if (ri < 0) { return; } PivotGridFieldMx f = Field = PivotGrid.Fields[ri] as PivotGridFieldMx; ResultsField rfld = f.ResultsField as ResultsField; AggregationDef agg = f.Aggregation; Mobius.Data.QueryColumn qc = rfld.QueryColumn; MetaColumn mc = qc.MetaColumn; GridColumn gc = hi.Column; if (gc == null) { return; } FieldGridRow = hi.RowHandle; FieldGridColumn = gc.AbsoluteIndex; GridViewInfo viewInfo = (GridViewInfo)FieldGridView.GetViewInfo(); GridCellInfo cellInfo = viewInfo.GetGridCellInfo(hi); Point menuLoc = new Point(cellInfo.Bounds.Left, cellInfo.Bounds.Bottom); menuLoc = FieldGrid.PointToScreen(menuLoc); if (gc.FieldName == "AggRoleCol") // show appropriate aggregation type menu { AggregationDefMenus.ShowRoleMenu(qc, f.Aggregation, menuLoc, AggregationRoleChanged); return; } else if (gc.FieldName == "AggTypeCol") // show appropriate aggregation type menu { AggregationDefMenus.ShowTypeMenu(qc, f.Aggregation, menuLoc, AggregationTypeChanged); return; } else if (gc.FieldName == "SourceColumnCol" || gc.FieldName == "SourceTableCol") { return; } }
/// <summary> /// Add a new pivot grid field /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void AddPivotField_Click(object sender, EventArgs e) { QueryManager qm = PivotView.Qm; Query q = qm.Query; FieldSelectorControl.Query = q; FieldSelectorControl.MetaColumn = null; Point p = AddPivotField.PointToScreen(new Point(0, AddPivotField.Height)); Mobius.Data.QueryColumn qc = FieldSelectorControl.ShowMenu(p); if (qc == null) { return; } PivotGridPropertiesMx pp = PivotView.PivotGridPropertiesMx; ResultsField rfld = qm.ResultsFormat.GetResultsField(qc); if (rfld == null) { return; } PivotGridFieldMx field = // add new field (must add to base view) PivotGridView.AddField(rfld, pp.PivotFields, null, GroupingTypeEnum.EqualValues); PivotGridFieldMx field2 = new PivotGridFieldMx(); field.CopyField(field2); pp.PivotFields.Remove(field); // remove from base view PivotGrid.BeginUpdate(); object ds = PivotGrid.DataSource; PivotGrid.DataSource = null; PivotGrid.Fields.Add(field2); // add to pivot grid PivotView.ConfigurePivotGridControlField(field2); PivotGrid.DataSource = ds; PivotGrid.EndUpdate(); FillFieldDetailsGrid(); return; }
/// <summary> /// Build the Grid fields for the PivotGridControl from the persisted Mobius view fields /// </summary> void BuildGridFieldsFromViewFields() { PivotGridFieldMx gf, gf2; PivotGridCtl.Fields.Clear(); PivotGridCtl.Groups.Clear(); PivotGridPropertiesMx p = PivotGridPropertiesMx; for (int fi = 0; fi < p.PivotFields.Count; fi++) // build grid fields from view fields { PivotGridFieldMx pf = p.PivotFields[fi]; ResultsField rfld = pf.ResultsField as ResultsField; if (rfld == null) { continue; } PivotGridControlMx.SetFieldCaption(pf); // be sure we have a caption Mobius.Data.QueryColumn qc = rfld.QueryColumn; MetaColumn mc = rfld.MetaColumn; gf = new PivotGridFieldMx(); pf.CopyField(gf); pf.SyncDxAreaToMxRole(); if (mc.IsKey) { gf.ImageIndex = (int)Bitmaps16x16Enum.Key; } else { gf.ImageIndex = (int)mc.DataTypeImageIndex; } gf.Options.AllowRunTimeSummaryChange = true; gf.Options.ShowUnboundExpressionMenu = true; PivotGridCtl.Fields.Add(gf); } return; }
/// <summary> /// Display cell text as hyperlinks to allow drilldown /// Also, set the background color if appropriate (e.g. bin that an SP/CRC value is in) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Grid_CustomAppearance(object sender, PivotCustomAppearanceEventArgs e) { e.Appearance.Font = new Font(e.Appearance.Font, FontStyle.Underline); e.Appearance.ForeColor = Color.Blue; PivotGridFieldContext f = GetPivotGridFieldContext(e.DataField); if (f == null) { return; } ResultsField rfld = f.ResultsField; if (rfld == null) { return; } Mobius.Data.QueryColumn qc = rfld.QueryColumn; MetaColumn mc = qc.MetaColumn; CondFormat cf = f.Qc.CondFormat; //if (cf == null || cf.Rules.Count == 0) return; object cv = e.GetCellValue(e.ColumnIndex, e.RowIndex); double d = -1; if (cv is int) { d = (int)cv; } else if (cv is double) { d = (double)cv; } else { return; } if (Math.Abs(1) == 2) // for compound by target { if (d >= 1 && d <= 10) { // todo: set proper color Color c = UnpivotedAssayResult.CalculateBinColor((int)d); e.Appearance.BackColor = c; } } if (DebugMx.False) // color by count { d = d / 45; if (d > 10) { d = 10; } if (d < 1) { d = 1; } if (d >= 1 && d <= 10) { // todo: set proper color Color c = UnpivotedAssayResult.CalculateBinColor((int)d); e.Appearance.BackColor = c; } } if (DebugMx.False) // for bin by target { PivotGridField[] rowFields = e.GetRowFields(); if (d > 0 && rowFields.Length > 0) { object rfv = e.GetFieldValue(rowFields[0]); if (rfv == null) { return; } else if (rfv is int) { Color c = UnpivotedAssayResult.CalculateBinColor((int)rfv); e.Appearance.BackColor = c; } } } }
/// <summary> /// Synchronize the Mobius pivot view field list with the source query fields /// </summary> /// <param name="query"></param> /// <param name="fieldsMx">The persisted PivotGridProperties.PivotFields for the Mobius view</param> public static void SyncMxFieldListWithSourceQuery( Query query, ref List <PivotGridFieldMx> fieldsMx) { PivotGridFieldMx f = null; int fi; Query q = query; QueryManager qm = q.QueryManager as QueryManager; if (qm == null) { return; } if (fieldsMx == null) { fieldsMx = new List <PivotGridFieldMx>(); } foreach (PivotGridFieldMx f0 in fieldsMx) { f0.ResultsField = null; } // Add any fields in the source that aren't in the list foreach (ResultsTable rt in qm.ResultsFormat.Tables) { QueryTable qt = rt.QueryTable; MetaTable mt = qt.MetaTable; foreach (ResultsField rfld in rt.Fields) { Mobius.Data.QueryColumn qc = rfld.QueryColumn; MetaColumn mc = qc.MetaColumn; string ufn = qt.Alias + "." + mc.Name + "."; // unbound name without unique id suffix if (mc.Name == "ACTIVITY_CLASS") { mc = mc; // debug } List <PivotGridFieldMx> matches = new List <PivotGridFieldMx>(); // list of pivot grid fields that match fields in the incoming results set foreach (PivotGridFieldMx f0 in fieldsMx) { // see if have already if (Lex.StartsWith(f0.UnboundFieldName, ufn)) { f0.ResultsField = rfld; // store associated results field matches.Add(f0); } } if (matches.Count > 0) { continue; } f = AddField(rfld, fieldsMx, null, GroupingTypeEnum.EqualValues); } } // Remove any fields in the list not in the source fi = 0; while (fi < fieldsMx.Count) { f = fieldsMx[fi]; if (f.ResultsField == null) { fieldsMx.Remove(f); // if no resultsField then not seen in source query } else { fi++; } } //if (Ctl.Groups.Count > 0) // debug //{ // g = Ctl.Groups[0]; // PivotGroupFields flds = (PivotGroupFields)g.Fields; // int fCnt = flds.Count; //} return; }
/// <summary> /// Value tentatively changed /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void FieldGridView_CellValueChanging(object sender, CellValueChangedEventArgs e) { int ri = e.RowHandle; if (ri < 0) { return; } DataRow dr = FieldGridDataTable.Rows[ri]; GridColumn gc = e.Column; if (gc == null) { return; } int gci = gc.AbsoluteIndex; PivotGridFieldMx f = PivotGrid.Fields[ri] as PivotGridFieldMx; ResultsField rfld = f.ResultsField as ResultsField; Mobius.Data.QueryColumn qc = rfld.QueryColumn; MetaColumn mc = qc.MetaColumn; if (gc.FieldName == "FieldNameCol") { return; // wait til move out of cell } else if (gc.FieldName == "AggRoleCol") { //e=e; //object o = e.Value; //DataRow dr2 = FieldGridDataTable.Rows[e.RowHandle]; //object vo = dr2["AggRoleCol"]; //FieldGridDataTable f.SyncDxAreaToMxRole(); // sync Dx area PivotGrid.RefreshData(); // refresh the PivotGrid to show new agg type results //dr["AggRoleCol"] = f.Aggregation.TypeLabel; return; // throw new NotImplementedException(); //string txt = e.Value.ToString(); //if (Lex.IsNullOrEmpty(txt) || Lex.Eq(txt, "None")) //{ // f.Visible = false; // dr["SummaryTypeCol"] = ""; // //dr["HeaderBinningCol"] = ""; //} //else //{ // f.Area = (PivotArea)Enum.Parse(typeof(PivotArea), txt); // f.Visible = true; // dr["SummaryTypeCol"] = SummaryTypeLabel(f); // //dr["HeaderBinningCol"] = GroupingTypeLabel(f); //} //PivotGrid.SetFilterHeaderVisibility(); } else if (gc.FieldName == "AggTypeCol") { return; } //else if (gc.FieldName == "HeaderBinningCol") //{ // return; //} else if (gc.FieldName == "SourceColumnCol" || gc.FieldName == "SourceTableCol") { return; } else { return; // something else } }
/// <summary> /// Sort a dataset /// </summary> /// <param name="results">Data to sort</param> /// <param name="sortColumns">Columns to sort on</param> /// <param name="query">Query information with vo positions of sort cols</param> /// <param name="keyValueVoPos">Index of added key value</param> /// <param name="ResultKeys">Ordered keys are returned here</param> /// <returns></returns> /// public List <object[]> Sort( List <object[]> results, List <SortColumn> sortColumns, Query query, int keyValueVoPos, out List <string> ResultKeys) { object [] vo, vo2; int qti, ri, ti, sci, i1, i2, i3; SortItem currentVal = null; // current top composite value (high or low) for current key object value; object [] sourceVo, destVo; string key; SortItem sItem = null, sItem2; int keyOffset = 0; // pk is stored in first element of vo and must be offset ValidateSortColumns(sortColumns); // Get array of sort columns associated with each metatable. This will be // used in the sort routine to do the local sorts in the context of // each table bool[][] mtSortCols = new bool[query.Tables.Count][]; MetaTableSortItems mtsi; int mtsiIdx; int firstQtWithSorting = -1; for (sci = 0; sci < sortColumns.Count; sci++) { QueryColumn qc = sortColumns[sci].QueryColumn; for (qti = 0; qti < query.Tables.Count; qti++) { // if (Query.Tables[qti] == qc.QueryTable) break; // (fails for preview) if (Lex.Eq((query.Tables[qti]).MetaTable.Name, qc.QueryTable.MetaTable.Name)) { break; // todo: properly handle when same metatable in query multiple times } } if (qti >= query.Tables.Count) { throw new Exception("Sort column not found in table"); } if (mtSortCols[qti] == null) // allocate array first time { mtSortCols[qti] = new bool[sortColumns.Count]; } mtSortCols[qti][sci] = true; if (firstQtWithSorting < 0) { firstQtWithSorting = qti; } } MultiColumnSortComparer sortComp = // sort including context of 1st table with sorting new MultiColumnSortComparer(sortColumns, mtSortCols[firstQtWithSorting]); // Build the array of values to be sorted. Each element of the array consists of // one or more primary values (i.e. the max or min value for each compound id // depending on the SortDirection for the column), the compound id and // one or more secondary values (i.e. the actual value for the sort key for that row) string currentKey = ""; int firstRowForKey = -1, rowsForKey; List <object> sArray = new List <object>(); object[] voResults = null; for (ri = 0; ri < results.Count; ri++) { vo = (object [])results[ri]; key = (string)vo[keyValueVoPos]; sItem = new SortItem(); sItem.SortValue = new IComparable[sortColumns.Count]; sItem.SortValueB = new IComparable[sortColumns.Count]; sArray.Add(sItem); sItem.Key = key; sItem.TupleIndex = ri; for (sci = 0; sci < sortColumns.Count; sci++) { // copy the primary & secondary values from result buffer into sort value array QueryColumn qc = sortColumns[sci].QueryColumn; SortOrder direction = sortColumns[sci].Direction; if (qc.IsKey) // if key be sure to get non-null value { sItem.SortValue[sci] = sItem.SortValueB[sci] = key; } else // copy non-key values { object o = null; if (qc.VoPosition >= 0 && qc.VoPosition < vo.Length) // be sure in range { o = vo[qc.VoPosition]; // get primitive or Mobius data type } else if (NullValue.IsNull(o)) { o = null; } else if (o is MobiusDataType) // convert Mobius types to a comparable primitive type { if (o is NumberMx) { o = (o as NumberMx).Value; } else if (o is QualifiedNumber) { QualifiedNumber qn = o as QualifiedNumber; if (MetaColumn.IsNumericMetaColumnType(qc.MetaColumn.DataType)) { o = qn.NumberValue; } else if (qc.MetaColumn.DataType == MetaColumnType.String) { o = qn.TextValue; } else { o = qn.NumberValue; // shouldn't happen } } else if (o is StringMx) { o = (o as StringMx).Value; } else if (o is DateTimeMx) { o = (o as DateTimeMx).Value; } else if (o is CompoundId) { o = (o as CompoundId).Value; } } else if (o is byte || o is sbyte || // convert all numbers to doubles so they compare properly (e.g. int that has cond formatting NumberMx values for some rows) o is Int16 || o is Int32 || o is Int64 || o is float || o is decimal) { o = Convert.ToDouble(o); } if (!(o is IComparable)) { o = null; // be sure it's a IComparable } sItem.SortValue[sci] = sItem.SortValueB[sci] = (IComparable)o; } } if (key != null && key != currentKey) // new key value { firstRowForKey = ri; currentKey = key; currentVal = sItem; } else // another tuple for same key { if (sortComp.CompareSortItems(currentVal, sItem) > 0) // new primary value? { currentVal = sItem; for (i1 = firstRowForKey; i1 < ri; i1++) { // reset primary value for preceeding rows for this key to the value of this row sItem2 = (SortItem)sArray[i1]; for (sci = 0; sci < sortColumns.Count; sci++) { sItem2.SortValue[sci] = currentVal.SortValue[sci]; } } } else // copy current primary values to this sort item { for (sci = 0; sci < sortColumns.Count; sci++) { sItem.SortValue[sci] = currentVal.SortValue[sci]; } } } } // Do the first sort which is overall for key order & relative for // first table with sort columns sArray.Sort(sortComp); // Reorder arraylist of raw tuples according to initial search results List <object[]> newResults = new List <object[]>(results.Count); for (ri = 0; ri < sArray.Count; ri++) { sItem = (SortItem)sArray[ri]; newResults.Add(results[sItem.TupleIndex]); } // Setup range in Vo for each query table and its key List <SortTableData> std = new List <SortTableData>(); foreach (QueryTable qt0 in query.Tables) { SortTableData st = new SortTableData(); std.Add(st); foreach (QueryColumn qc0 in qt0.QueryColumns) { MetaColumn mc = qc0.MetaColumn; if (mc.IsKey) { st.KeyColPos = qc0.VoPosition; } if (qc0.VoPosition >= 0 && st.FirstColumn < 0) { st.FirstColumn = qc0.VoPosition; } if (qc0.Selected) { st.SelectCount++; } } } // Do table-relative sort for other tables with sort fields ArrayList CopyBuf = new ArrayList(); // used for copying int voLen = 0; // length of vo if (results != null && results.Count > 0 && results[0] != null) { voLen = ((object [])results[0]).Length; } for (ti = 0; ti < std.Count; ti++) // check all tables { int voTablePos = std[ti].FirstColumn; // +std[ti].KeyColPos + keyOffset; // position of first element of vo for table int voTableLen = std[ti].SelectCount; if (ti == firstQtWithSorting) { continue; // did this table first } else if (mtSortCols[ti] == null) { continue; // no sorting on this one } sortComp = // proper sort comparer for this table new MultiColumnSortComparer(sortColumns, mtSortCols[ti]); sArray.Sort(sortComp); // re-sort original results for this table currentKey = ""; firstRowForKey = -1; rowsForKey = 0; for (ri = 0; ri <= sArray.Count; ri++) // reorder table vo entries for new results { if (ri < sArray.Count) { sItem = (SortItem)sArray[ri]; vo = (object [])results[sItem.TupleIndex]; // address the vo key = (string)vo[keyValueVoPos]; } else { key = "<end>"; // past end of data, force copy back of last chunk } if (key != null && key != currentKey) // new key value { if (firstRowForKey >= 0) // anything to copy back { for (i1 = 0; i1 < rowsForKey; i1++) { // copy back to results buffer in proper order sourceVo = (Object[])CopyBuf[i1]; destVo = (Object[])newResults[firstRowForKey + i1]; Array.Copy(sourceVo, voTablePos, destVo, voTablePos, voTableLen); } } if (ri >= sArray.Count) { break; // really done? } currentKey = key; firstRowForKey = ri; // first row in newResults for the key rowsForKey = 0; } if (CopyBuf.Count < rowsForKey + 1) // be sure copy buffer big enough { CopyBuf.Add(new object[voLen]); } sourceVo = (Object[])results[sItem.TupleIndex]; // copy from original results row to buffer destVo = (Object[])CopyBuf[rowsForKey]; Array.Copy(sourceVo, voTablePos, destVo, voTablePos, voTableLen); rowsForKey++; } // end of loop on results entries } // end of loop on query tables // For each key shift non-null metatable data to top of section for key ResultKeys = new List <string>(); // reorder result keys also currentKey = ""; for (ri = 0; ri < newResults.Count; ri++) { vo = (object [])newResults[ri]; key = (string)vo[keyValueVoPos]; if (key != null && key != currentKey) // new key value { firstRowForKey = ri; currentKey = key; ResultKeys.Add(key); } //else // another row for key, shift up data as needed to fill gaps { if (query.Tables.Count <= 1) { continue; // only need to do if more than one table } for (ti = 0; ti < std.Count; ti++) // check all tables (sorted tables should be ok except for root table is sorted on key { int voTablePos = std[ti].FirstColumn; // +std[ti].KeyColPos + keyOffset; // position of first element of vo for table int voTableLen = std[ti].SelectCount; if (NullValue.IsNull(vo[voTablePos])) { continue; // skip if null data for this table (i.e. key is null) } for (i2 = firstRowForKey; i2 < ri; i2++) // look for empty slot { vo2 = (object [])newResults[i2]; object o = vo2[voTablePos]; if (!NullValue.IsNull(o)) { continue; // already full } Array.Copy(vo, voTablePos, vo2, voTablePos, voTableLen); Array.Clear(vo, voTablePos, voTableLen); break; } } } } return(newResults); // substitute new ordered set of results }