/// <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)))
));
}
