/// <summary> /// Changes the axis column in the GROUP BY section of the query (e.g. "[MyDb]..[mytbl].[AdmissionDate],") and /// the axis column in the SELECT section of the query (e.g. "[MyDb]..[mytbl].[AdmissionDate] as Admt,") with /// the appropriate axis increment (e.g. "YEAR([MyDb]..[mytbl].[AdmissionDate])," and "YEAR([MyDb]..[mytbl].[AdmissionDate]) as Admt,") /// </summary> /// <param name="axisColumn"></param> /// <param name="lines"></param> /// <param name="axis"></param> /// <param name="axisColumnWithoutAlias"></param> /// <param name="axisColumnAlias"></param> protected void WrapAxisColumnWithDatePartFunction(CustomLine axisColumn, List <CustomLine> lines, IQueryAxis axis, string axisColumnWithoutAlias, string axisColumnAlias) { var axisGroupBy = lines.Single(l => l.LocationToInsert == QueryComponent.GroupBy && l.Role == CustomLineRole.Axis); var axisColumnEndedWithComma = axisColumn.Text.EndsWith(","); axisColumn.Text = GetDatePartOfColumn(axis.AxisIncrement, axisColumnWithoutAlias) + " AS " + axisColumnAlias + (axisColumnEndedWithComma ? "," : ""); var groupByEndedWithComma = axisGroupBy.Text.EndsWith(","); axisGroupBy.Text = GetDatePartOfColumn(axis.AxisIncrement, axisColumnWithoutAlias) + (groupByEndedWithComma ? "," : ""); }
bool IsCollucion(CustomLine line, CustomWord word) { if (word.X >= line.X && (word.X <= line.X + line.Width) && word.Y >= line.Y && word.Y <= line.Y + line.Height) { return(true); } //if (word.X >= line.X && (word.X <= line.X + line.Width) && word.Y >= line.Y && word.Y <= line.Y + line.Height && word.X + word.Width < line.X + line.Width) //{ // return true; //} return(false); }
public void testNormalIntersection() { Point p1 = new Point(0, 100); Point p2 = new Point(100, 0); Point p3 = new Point(0, 0); Point p4 = new Point(100, 100); Point p5 = new Point(50, 50); CustomLine l1 = new CustomLine(p1, p2); CustomLine l2 = new CustomLine(p3, p4); Point? intersectionPoint = CustomLine.getIntersectionCoordinates(l1, l2); Assert.IsTrue(intersectionPoint.HasValue); Assert.AreEqual(p5.X, intersectionPoint.Value.X); Assert.AreEqual(p5.Y, intersectionPoint.Value.Y); }
public void testOneVerticalIntersection() { // horizontal line Point p1 = new Point(0, 100); Point p2 = new Point(10, 100); // vertical lines Point p3 = new Point(5, 0); Point p4 = new Point(5, 100); CustomLine l1 = new CustomLine(p1, p2); CustomLine l2 = new CustomLine(p3, p4); Point? intersectionPoint = CustomLine.getIntersectionCoordinates(l1, l2); Assert.IsTrue(intersectionPoint.HasValue); Assert.AreEqual(p3.X, intersectionPoint.Value.X); Assert.AreEqual(p1.Y, intersectionPoint.Value.Y); }
/// <summary> /// Add a custom line of code into the query at the specified position. This will be maintained throughout the lifespan of the object such that if /// you add other columns etc then your code will still be included at the appropriate position. /// </summary> /// <param name="builder"></param> /// <param name="text"></param> /// <param name="positionToInsert"></param> public static CustomLine AddCustomLine(ISqlQueryBuilder builder, string text, QueryComponent positionToInsert) { CustomLine toAdd = new CustomLine(text, positionToInsert); if (positionToInsert == QueryComponent.GroupBy || positionToInsert == QueryComponent.OrderBy || positionToInsert == QueryComponent.FROM || positionToInsert == QueryComponent.Having) { throw new QueryBuildingException("Cannot inject custom lines into QueryBuilders at location " + positionToInsert); } if (positionToInsert == QueryComponent.WHERE) { if (text.Trim().StartsWith("AND ") || text.Trim().StartsWith("OR ")) { throw new Exception("Custom filters are always AND, you should not specify the operator AND/OR, you passed\"" + text + "\""); } } builder.CustomLines.Add(toAdd); return(toAdd); }
/// <summary> /// Provides useful bits of SQL you need to build an axis only aggregate /// </summary> /// <param name="syntaxHelper">Your DBMS specific syntax helper</param> /// <param name="lines">The lines you were given in <see cref="BuildAggregate"/></param> /// <param name="countSelectLine">The single aggregate function line e.g. "count(distinct chi) as Fish,"</param> /// <param name="countSqlWithoutAlias">The portion of <paramref name="countSelectLine"/> which excludes the alias e.g. "count(distinct chi)"</param> /// <param name="countAlias">The portion of <paramref name="countSelectLine"/> which is the alias e.g. "Fish" (or null if no AS is specified)</param> /// <param name="axisColumn">The single line of SELECT SQL which is the Axis join column e.g. "[MyDb]..[mytbl].[AdmissionDate] as Admt,"</param> /// <param name="axisColumnWithoutAlias">The portion of <paramref name="axisColumn"/> which excludes the alias e.g. "[MyDb]..[mytbl].[AdmissionDate]"</param> /// <param name="axisColumnAlias">The portion of <paramref name="axisColumn"/> which is the alias e.g. "Admt" (or "joinDt" if no AS is specified)</param> protected void GetAggregateAxisBits(IQuerySyntaxHelper syntaxHelper, List <CustomLine> lines, out CustomLine countSelectLine, out string countSqlWithoutAlias, out string countAlias, out CustomLine axisColumn, out string axisColumnWithoutAlias, out string axisColumnAlias) { countSelectLine = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.CountFunction); syntaxHelper.SplitLineIntoSelectSQLAndAlias(countSelectLine.Text, out countSqlWithoutAlias, out countAlias); //Deal with the axis dimension which is currently `mydb`.`mytbl`.`mycol` and needs to become YEAR(`mydb`.`mytbl`.`mycol`) As joinDt axisColumn = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.Axis); syntaxHelper.SplitLineIntoSelectSQLAndAlias(axisColumn.Text, out axisColumnWithoutAlias, out axisColumnAlias); if (string.IsNullOrWhiteSpace(axisColumnAlias)) { axisColumnAlias = "joinDt"; } }
private void FlagLineBasedOnIcolumn(CustomLine line, IColumn column) { //if it is an axis column tag it as an axis if (Equals(column, _axisAppliesToDimension)) { line.Role = CustomLineRole.Axis; } //if it is a count column then flag it as that (cic aggregates take extreme liberties with count columns like hijacking them in a most dispicable way so don't even bother with this flag for them) if (column is AggregateCountColumn && !_isCohortIdentificationAggregate) { line.Role = CustomLineRole.CountFunction; } if (_pivotDimension != null) { if (Equals(column, _pivotDimension.IColumn)) { line.Role = CustomLineRole.Pivot; } } }
private void GetGroupBySQL(List <CustomLine> queryLines, IAggregateHelper aggregateHelper) { //now are there columns that... if (SelectColumns.Count(col => !(col.IColumn is AggregateCountColumn) //are not count(*) style columns && !_skipGroupByForThese.Contains(col.IColumn)) > 0) //and are not being skipped for GROUP BY { //yes there are! better group by then! queryLines.Add(new CustomLine("group by ", QueryComponent.GroupBy)); foreach (var col in SelectColumns) { if (col.IColumn is AggregateCountColumn) { continue; } //was added with skip for group by enabled if (_skipGroupByForThese.Contains(col.IColumn)) { continue; } string select; string alias; _syntaxHelper.SplitLineIntoSelectSQLAndAlias(col.GetSelectSQL(null, null, _syntaxHelper), out select, out alias); var line = new CustomLine(select + ",", QueryComponent.GroupBy); FlagLineBasedOnIcolumn(line, col.IColumn); queryLines.Add(line); } //clear trailing last comma queryLines.Last().Text = queryLines.Last().Text.TrimEnd('\n', '\r', ','); queryLines.Add(new CustomLine(GetHavingSql(), QueryComponent.Having)); CompileCustomLinesInStageAndAddToList(QueryComponent.GroupBy, queryLines); //order by only if we are not pivotting if (!DoNotWriteOutOrderBy) { queryLines.Add(new CustomLine("order by ", QueryComponent.OrderBy)); //if theres a top X (with an explicit order by) if (AggregateTopX != null) { queryLines.Add(new CustomLine(GetOrderBySQL(AggregateTopX), QueryComponent.OrderBy) { Role = CustomLineRole.TopX }); } else { foreach (var col in SelectColumns) { if (col.IColumn is AggregateCountColumn) { continue; } //was added with skip for group by enabled if (_skipGroupByForThese.Contains(col.IColumn)) { continue; } string select; string alias; _syntaxHelper.SplitLineIntoSelectSQLAndAlias(col.GetSelectSQL(null, null, _syntaxHelper), out select, out alias); var line = new CustomLine(select + ",", QueryComponent.OrderBy); FlagLineBasedOnIcolumn(line, col.IColumn); queryLines.Add(line); } } queryLines.Last().Text = queryLines.Last().Text.TrimEnd(','); } } else { queryLines.Add(new CustomLine(GetHavingSql(), QueryComponent.GroupBy)); } queryLines.Last().Text = queryLines.Last().Text.TrimEnd('\n', '\r', ','); CompileCustomLinesInStageAndAddToList(QueryComponent.Postfix, queryLines); }
private List <CustomLine> buildTextFromParagraph(FREngine.IParagraph par, int iBlock) { // CustomParagraph result = new CustomParagraph(); List <CustomWord> wordItems = new List <CustomWord>(); string text = par.Text; FREngine.CharParams charParams = par.Application.CreateCharParams(); int wordStart = 0; int wordStartInText = Text.Count; List <Character> charaterItems = new List <Character>(); for (int iChar = 0; iChar < text.Length; iChar++) { char ch = text[iChar]; par.GetCharParams(iChar, charParams); bool isVisible = (!char.IsWhiteSpace(ch)); if (isVisible) { Text.Add(new Character(ch, charParams)); charaterItems.Add(new Character(ch, charParams)); } // Splitting words by whitespaces // TODO: line break, hyphen if (char.IsWhiteSpace(ch)) { if (wordStart < iChar) { int wordEndInText = Text.Count - 1; words.Add(new Word(wordStartInText, wordEndInText, Text)); CustomWord word = new CustomWord(); //word.Word = new Word(wordStartInText, wordEndInText, Text); word.X = charaterItems.Min(x => x.Rect.X); word.Y = charaterItems.Min(x => x.Rect.Y); word.Value = new Word(wordStartInText, wordEndInText, Text).ToString(); int xMax = charaterItems.Max(x => x.Rect.X); word.Width = xMax + charaterItems.First(x => x.Rect.X == xMax).Rect.Width - word.X; word.Height = charaterItems.Max(x => x.Rect.Height); //word.CharaterItems = charaterItems; wordItems.Add(word); listWords.Add(word); charaterItems = new List <Character>(); } wordStart = iChar + 1; wordStartInText = Text.Count; } } // Add last word if necessary if (wordStart < text.Length) { int wordEndInText = Text.Count - 1; words.Add(new Word(wordStartInText, wordEndInText, Text)); CustomWord word = new CustomWord(); //word.Word = new Word(wordStartInText, wordEndInText, Text); //word.CharaterItems = charaterItems; word.Value = new Word(wordStartInText, wordEndInText, Text).ToString(); word.X = charaterItems.Min(x => x.Rect.X); word.Y = charaterItems.Min(x => x.Rect.Y); int xMax = charaterItems.Max(x => x.Rect.X); word.Width = xMax + charaterItems.First(x => x.Rect.X == xMax).Rect.Width - word.X; word.Height = charaterItems.Max(x => x.Rect.Height); wordItems.Add(word); listWords.Add(word); charaterItems = new List <Character>(); } List <CustomLine> csLineItems = new List <CustomLine>(); //if (result.WordItems.Any()) //{ // CustomLine line = new CustomLine() // { // X = par.Left, // Y = par.Top, // Width = par.Right - par.Left, // Height = par.Bottom - par.Top, // WordItems = result.WordItems, // Index = 0, // ParentBlockIndex = iBlock, // Value = string.Join(" ", result.WordItems.Select(x => x.Word.ToString()).ToArray()) // } // ; // csLineItems.Add(line); // lineAll.Add(line); //} FREngine.IParagraphLines lines = par.Lines; if (wordItems.Any()) { int iLine = 0; foreach (FREngine.IParagraphLine item in lines) { CustomLine lineItem = new CustomLine() { X = item.Left, Y = item.Top, Height = item.Bottom - item.Top, Width = item.Right - item.Left, ParentIndex = 0, Index = iLine, }; int iWords = 0; lineItem.WordItems = wordItems.Where(x => IsCollucion(lineItem, x)).Select(x => { x.Index = iWords; iWords++; x.ParentIndex = iLine; return(x); }).ToList(); lineItem.Value = string.Join(" ", lineItem.WordItems.OrderBy(x => x.X).Select(x => x.Value)); lineItem.ValueWithoutSpace = string.Join("", lineItem.WordItems.OrderBy(x => x.X).Select(x => x.Value)); csLineItems.Add(lineItem); lineAll.Add(lineItem); iLine++; } } return(csLineItems.OrderBy(x => x.Index).ToList()); }
protected override string BuildPivotOnlyAggregate(AggregateCustomLineCollection query, CustomLine nonPivotColumn) { throw new NotImplementedException(); }
protected override string BuildPivotOnlyAggregate(AggregateCustomLineCollection query, CustomLine nonPivotColumn) { IQuerySyntaxHelper syntaxHelper = query.SyntaxHelper; string pivotAlias; string countAlias; string axisColumnAlias; var part1 = GetPivotPart1(query, out pivotAlias, out countAlias, out axisColumnAlias); string nonPivotColumnAlias; string nonPivotColumnSelect; syntaxHelper.SplitLineIntoSelectSQLAndAlias(nonPivotColumn.Text, out nonPivotColumnSelect, out nonPivotColumnAlias); //ensure we have an alias for the non pivot column if (string.IsNullOrWhiteSpace(nonPivotColumnAlias)) { nonPivotColumnAlias = syntaxHelper.GetRuntimeName(nonPivotColumnSelect); } //The dynamic query in which we assemble a query string and EXECUTE it string part2 = string.Format(@" /*DYNAMIC PIVOT*/ declare @Query varchar(MAX) SET @Query = ' {0} /*Would normally be Select * but must make it IsNull to ensure we see 0s instead of null*/ select {1}, '+@FinalSelectList+' from ( {2} ) s PIVOT ( sum({3}) for {4} in ('+@Columns+') --The dynamic Column list we just fetched at top of query ) piv ORDER BY {1}' EXECUTE(@Query) ", //anything before the SELECT (i.e. parameters) syntaxHelper.Escape(string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert < QueryComponent.SELECT))), syntaxHelper.Escape(nonPivotColumnAlias), //the entire select query up to the end of the group by (ommitting any Top X) syntaxHelper.Escape(string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert >= QueryComponent.SELECT && c.LocationToInsert < QueryComponent.OrderBy && c.Role != CustomLineRole.TopX))), syntaxHelper.Escape(countAlias), syntaxHelper.Escape(pivotAlias)); return(part1 + part2); }
public txUGUICustomLine() { mCustomLine = new CustomLine(); }
private void AdjustAxisQueryLines(List <CustomLine> lines, IQueryAxis axis, IQuerySyntaxHelper syntaxHelper, out CustomLine axisColumn, out string axisColumnWithoutAlias, out string axisColumnAlias, out CustomLine axisGroupBy) { //Deal with the axis dimension which is currently [mydb].[mytbl].[mycol] and needs to become YEAR([mydb].[mytbl].[mycol]) As joinDt axisColumn = lines.Single(l => l.LocationToInsert == QueryComponent.QueryTimeColumn && l.Role == CustomLineRole.Axis); syntaxHelper.SplitLineIntoSelectSQLAndAlias(axisColumn.Text, out axisColumnWithoutAlias, out axisColumnAlias); axisGroupBy = lines.Single(l => l.LocationToInsert == QueryComponent.GroupBy && l.Role == CustomLineRole.Axis); if (string.IsNullOrWhiteSpace(axisColumnAlias)) { axisColumnAlias = "joinDt"; } var axisColumnEndedWithComma = axisColumn.Text.EndsWith(","); axisColumn.Text = GetDatePartOfColumn(axis.AxisIncrement, axisColumnWithoutAlias) + " AS " + axisColumnAlias + (axisColumnEndedWithComma ? "," : ""); var groupByEndedWithComma = axisGroupBy.Text.EndsWith(","); axisGroupBy.Text = GetDatePartOfColumn(axis.AxisIncrement, axisColumnWithoutAlias) + (groupByEndedWithComma ? "," : ""); }
public LineFigure(RenderTarget render) : base(render) { _line = new CustomLine(); }
public void DrawLinearPart() { if (curve != null) { if (lineCurve != null) { planeGraph.GraphPane.CurveList.Remove(lineCurve); curvesDropDownBtn.DropDownItems.Remove(linearPart.button); buttons.Remove(linearPart); linearPart = null; lineCurve = null; } linearPart = GraphProcessing.CreateCurve(ref lineCurve, curvesDropDownBtn, planeGraph, "Линейный участок", Color.Blue, 8, SymbolType.Circle, Color.Blue); buttons.Add(linearPart); lineCurve.Label.IsVisible = false; LineItem firstDerivativeCurve = new LineItem("d1"); firstDerivativeCurve.Tag = 5; LineItem firstMovingAverageCurve = new LineItem("mad1"); firstMovingAverageCurve.Tag = 5; processedCurve = new LineItem("movingAverage1"); processedCurve.Tag = 5; processedCurve2 = new LineItem("movingAverage2"); processedCurve2.Tag = 5; double[] data = GraphProcessing.CurveToArray(curve, false); double[] movingAverage1 = GraphProcessing.MovingAverage(data, sensitivy); for (int i = 0; i < movingAverage1.Length - 1; i++) { processedCurve2.AddPoint(curve.Points[i].X, movingAverage1[i]); } linearPart.curve.IsVisible = false; processedCurve = processedCurve2.Clone(); GraphProcessing.DerivativeGraph(curve, ref firstDerivativeCurve); GraphProcessing.SecondDerivativeGraph(firstDerivativeCurve, ref secondDerivativeCurve); GraphProcessing.DerivativeGraph(processedCurve, ref firstMovingAverageCurve); LineItem tempCurve = new LineItem("temp"); tempCurve.Tag = 5; LineItem secondMovingAverageCurve = new LineItem("sMAC"); secondMovingAverageCurve.Tag = 5; GraphProcessing.DerivativeGraph(processedCurve2, ref tempCurve); GraphProcessing.DerivativeGraph(tempCurve, ref secondMovingAverageCurve); int[] bounds = GraphProcessing.CalculatePointsForLinear(curve, secondMovingAverageCurve, firstMovingAverageCurve); for (int i = bounds[0]; i < bounds[1]; i++) { lineCurve.AddPoint(curve[i]); } aproximateLinearCurve = null; int begin = (int)bounds[0]; int end = (int)bounds[1]; buttons.Add(GraphProcessing.CreateCurve(ref aproximateLinearCurve, curvesDropDownBtn, planeGraph, "Линейный участок МНК", Color.Green, 2, SymbolType.Circle, Color.Green)); //(int) curve.Points[begin].X (int) curve.Points[end].X // y= y1+(x-x1) (y2 - y1) / (x2-x1) ; y = k*(x-x1) + y1 = kx - (kx1 + y1) MyMath.leastSquaresBuild((int)curve.Points[begin].X, (int)curve.Points[end].X, lineCurve, ref aproximateLinearCurve, this); aproximateLinearCurve.Tag = 2; form.AproximateLinearCurve = aproximateLinearCurve; aproximateLinearCurve.IsVisible = false; aproximateLinearCurve.Label.IsVisible = false; lineCurve.Tag = 5; planeGraph.Refresh(); GraphProcessing.UpdateGraph(planeGraph); } else { ErrorMessage form = new ErrorMessage("Нет графика на котором нужно найти линейный участок"); } Task.Delay(1500); var customLineForm = new CustomLine(this); customLineForm.Show(); customLineForm.Activate(); customLineForm.Focus(); customLineForm.BringToFront(); }
protected abstract string BuildPivotOnlyAggregate(AggregateCustomLineCollection query, CustomLine nonPivotColumn);
protected override string BuildPivotOnlyAggregate(AggregateCustomLineCollection query, CustomLine nonPivotColumn) { string part1 = GetPivotPart1(query); string nonPivotColumnSql = nonPivotColumn.GetTextWithoutAlias(query.SyntaxHelper); return(string.Format(@" {0} {1} SET @sql = CONCAT( ' SELECT {2}',@columnsSelectCases,' {3} GROUP BY {4} ORDER BY {4} {5} '); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;", string.Join(Environment.NewLine, query.Lines.Where(l => l.LocationToInsert < QueryComponent.SELECT)), part1, nonPivotColumn, //everything inclusive of FROM but stopping before GROUP BY query.SyntaxHelper.Escape(string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert >= QueryComponent.FROM && c.LocationToInsert < QueryComponent.GroupBy))), nonPivotColumnSql, //any HAVING SQL query.SyntaxHelper.Escape(string.Join(Environment.NewLine, query.Lines.Where(c => c.LocationToInsert == QueryComponent.Having))) )); }