private List <string> GetParameterReady()
{
List <string> p = new List <string>();
p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c =>
{
if (c.IsContinuous)
{
return("0");
}
else
{
return(c.KindCount.ToString());
}
}).ToArray()));
p.Add(string.Join(",", AndColumns.Select(c => c.Name.ToUpper()).ToArray()));
p.Add(string.Join(",", AndColumns.Select(c => c.Rem).ToArray()));
p.Add(string.Join(",", AndColumns.Select(c =>
{
if (c.IsContinuous)
{
return("0");
}
else
{
return(c.KindCount.ToString());
}
}).ToArray()));
return(p);
}
private string BuildSelectClause()
{
return(string.Join(",", SelectedColumns.Select(s =>
{
var tableName = (string.IsNullOrWhiteSpace(s.TableName) || s.TableName == FromTableName) ? FromTableAlias : s.TableName;
return $"[{tableName}].[{s.ColumnName}] AS [{s.AliasName}]";
})));
}
private List <string> GetParameterReady()
{
List <string> p = new List <string>();
p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c => c.KindCount).ToArray()));
p.Add(FilterValue.ToString());
return(p);
}
private List <string> GetParameterReady()
{
List <string> p = new List <string>();
p.Add(ResultColumn.Name.ToUpper());
p.Add(ResultColumn.Rem);
p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c => c.KindCount).ToArray()));
p.Add(DataType_ClassMethod.ToString());
return(p);
}
private List <string> GetParameterReady()
{
List <string> p = new List <string>();
p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c =>
{
if (c.IsContinuous)
{
return("0");
}
else
{
return(c.KindCount.ToString());
}
}).ToArray()));
p.Add(jyfield.ToString());
p.Add(jzfield.ToString());
if (IsSelectGroup > 0)
{
p.Add("1");
p.Add(GroupingColumn.Name.ToUpper() + "|" +
string.Join("|", GroupingColumn.GroupingTags.Select(c => GroupingColumn.Name.ToUpper() + "." + c.Index).ToArray()));
//p.Add(GroupingColumn.Name.ToUpper());
p.Add(GroupingColumn.Rem + "|" +
string.Join("|", GroupingColumn.GroupingTags.Select(c => " " + c.Index).ToArray()));
//p.Add(GroupingColumn.Rem.ToUpper());
}
else
{
p.Add("0");
}
//var sbName = new List<string>();
//sbName.Add(GroupingColumn.Name.ToUpper());
//foreach (var item in GroupingTags)
//{
// sbName.Add(GroupingColumn.Name + "." + item.Index);
//}
//var sbLabel = new List<string>();
//sbLabel.Add(GroupingColumn.Rem);
//foreach (var item in GroupingTags)
//{
// sbLabel.Add(" " + item.Name);
//}
//p.Add(string.Join("|", sbName));
//p.Add(string.Join("|", sbLabel));
return(p);
}
private List <string> GetParameterReady()
{
List <string> p = new List <string>();
p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c => c.KindCount).ToArray()));
foreach (var item in RelativeColumns)
{
p.Add(item.DistroType);
p.Add(item.LinkFunction);
}
p.Add(string.Join(",", RelativeColumns.Select(c => c.Name.ToUpper()).ToArray()));
p.Add(string.Join(",", RelativeColumns.Select(c => c.Rem).ToArray()));
p.Add(string.Join(",", RelativeColumns.Select(c => c.KindCount).ToArray()));
p.Add(jyField.ToString());
return(p);
}
private List <string> GetParameterReady()
{
List <string> p = new List <string>();
List <string> nameList = new List <string>();
foreach (var item in SelectedColumns)
{
nameList.Add(item.Name.ToUpper() + "|" +
string.Join("|", item.GroupingTags.Select(c => item.Name.ToUpper() + "." + c.Index).ToArray()));
}
p.Add(string.Join("|", nameList.ToArray()));
List <string> remList = new List <string>();
foreach (var item in SelectedColumns)
{
remList.Add(item.Rem + "|" +
string.Join("|", item.GroupingTags.Select(c => " " + c.Index).ToArray()));
}
p.Add(string.Join("|", remList.ToArray()));
//p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray()));
//p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c =>
{
if (c.IsContinuous)
{
return("0");
}
else
{
return(c.KindCount.ToString());
}
}).ToArray()));
p.Add(rytxt);
return(p);
}
private List <string> GetParameterReady()
{
List <string> p = new List <string>();
p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c =>
{
if (c.IsContinuous)
{
return("0");
}
else
{
return(c.KindCount.ToString());
}
}).ToArray()));
var sbName = new List <string>();
sbName.Add(GroupingColumn.Name.ToUpper());
foreach (var item in GroupingTags)
{
sbName.Add(GroupingColumn.Name.ToUpper() + "." + item.Index);
}
var sbLabel = new List <string>();
sbLabel.Add(GroupingColumn.Rem);
foreach (var item in GroupingTags)
{
sbLabel.Add(" " + item.Name);
}
p.Add(string.Join("|", sbName));
p.Add(string.Join("|", sbLabel));
p.Add(bzField.ToString());
p.Add(jyField.ToString());
return(p);
}
private List <string> GetParameterReady()
{
List <string> p = new List <string>();
p.Add(string.Join(",", SelectedColumns.Select(c => c.Name.ToUpper()).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c => c.Rem).ToArray()));
p.Add(string.Join(",", SelectedColumns.Select(c =>
{
if (c.IsContinuous)
{
return("0");
}
else
{
return(c.KindCount.ToString());
}
}).ToArray()));
p.Add(GroupingColumn.Name.ToUpper());
p.Add(GroupingColumn.Rem);
p.Add(scField.ToString());
p.Add(jqField.ToString());
p.Add(rowGroupSign.ToString());
if (rowGroupSign == 1)
{
p.Add(GroupingRowColumn.Name.ToUpper());
p.Add(GroupingRowColumn.Rem);
}
else
{
p.Add("NA");
p.Add("NA");
}
return(p);
}
/// <inheritdoc />
public override string Build()
{
var sb = new StringBuilder("SELECT ");
// Output Distinct.
if (IsDistinct)
{
sb.Append("DISTINCT ");
}
// Output column names.
sb.Append(SelectedColumns.Any() ? string.Join(", ", SelectedColumns.Select(WrapVariable)) : "*");
// Output table names.
if (SelectedTables.Any())
{
sb.Append($" FROM {string.Join(", ", SelectedTables.Select(WrapVariable))}");
}
// Output joins.
if (JoinStatement.Any())
{
foreach (var clause in JoinStatement)
{
sb.AppendLine();
switch (clause.JoinType)
{
case JoinType.InnerJoin:
sb.Append("INNER JOIN ");
break;
case JoinType.LeftJoin:
sb.Append("LEFT JOIN ");
break;
case JoinType.RightJoin:
sb.Append("RIGHT JOIN ");
break;
default:
throw new ArgumentOutOfRangeException(nameof(clause.JoinType), $"MySql doesn't support {clause.JoinType} join type.");
}
sb.Append($"`{clause.ToTable}` ON ");
sb.Append(CreateComparisonClause(
$"{clause.ToTable}.{clause.ToColumn}",
clause.ComparisonOperator,
new SqlLiteral($"{clause.FromTable}.{clause.FromColumn}")));
}
}
// Output where statement.
if (WhereStatement.Any())
{
sb.AppendLine();
sb.Append($"WHERE {string.Join(" AND ", WhereStatement.Select(BuildWhereClauseString))}");
}
// Output GroupBy statement.
if (GroupByColumns.Count > 0)
{
sb.AppendLine();
sb.Append($"GROUP BY {string.Join(", ", GroupByColumns.Select(WrapVariable))}");
}
// Output OrderBy statement.
if (OrderByStatement.Any())
{
sb.AppendLine();
sb.Append($"ORDER BY {string.Join(", ", OrderByStatement.Select(BuildOrderByClauseString))}");
}
if (TakeRows.HasValue)
{
sb.AppendLine();
if (SkipRows.HasValue)
{
sb.Append($"LIMIT {SkipRows}, {TakeRows}");
}
else
{
sb.Append($"LIMIT {TakeRows}");
}
}
// Return the built query.
return(sb.ToString());
}
/// <inheritdoc />
public override string Build()
{
var sb = new StringBuilder("SELECT ");
// Output Distinct.
if (IsDistinct)
{
sb.Append("DISTINCT ");
}
if (!SkipRows.HasValue && TakeRows.HasValue)
{
sb.Append($"TOP {TakeRows} ");
if (TopIsPercent)
{
sb.Append("PERCENT ");
}
}
// Output column names.
sb.Append(SelectedColumns.Any() ? string.Join(", ", SelectedColumns.Select(WrapVariable)) : "*");
// Output table names.
if (SelectedTables.Any())
{
sb.Append($" FROM {string.Join(", ", SelectedTables.Select(WrapVariable))}");
}
// Output joins.
if (JoinStatement.Any())
{
foreach (var clause in JoinStatement)
{
sb.AppendLine();
switch (clause.JoinType)
{
case JoinType.InnerJoin:
sb.Append("INNER JOIN ");
break;
case JoinType.OuterJoin:
sb.Append("OUTER JOIN ");
break;
case JoinType.LeftJoin:
sb.Append("LEFT JOIN ");
break;
case JoinType.RightJoin:
sb.Append("RIGHT JOIN ");
break;
}
sb.Append($"[{clause.ToTable}] ON ");
sb.Append(CreateComparisonClause(
$"{clause.ToTable}.{clause.ToColumn}",
clause.ComparisonOperator,
new SqlLiteral($"{clause.FromTable}.{clause.FromColumn}")));
}
}
// Output where statement.
if (WhereStatement.Any())
{
sb.AppendLine();
sb.Append($"WHERE {string.Join(" AND ", WhereStatement.Select(BuildWhereClauseString))}");
}
// Output GroupBy statement.
if (GroupByColumns.Count > 0)
{
sb.AppendLine();
sb.Append($"GROUP BY {string.Join(", ", GroupByColumns.Select(WrapVariable))}");
}
// TODO: Output having statement.
/*
* if (Having.ClauseLevels > 0)
* {
* // Check if a Group By Clause was set
* if (groupByColumns.Count == 0)
* {
* throw new Exception("Having statement was set without Group By");
* }
* if (buildCommand)
* {
* sb.Append(" HAVING " + Having.BuildWhereStatement(() => command));
* }
* else
* {
* sb.Append(" HAVING " + Having.BuildWhereStatement());
* }
* }
*/
// Output OrderBy statement.
if (OrderByStatement.Any())
{
sb.AppendLine();
sb.Append($"ORDER BY {string.Join(", ", OrderByStatement.Select(BuildOrderByClauseString))}");
// Works only in SQL Server 2012 and upper.
// TODO use BETWEEN if it require
if (SkipRows.HasValue)
{
sb.AppendLine();
sb.Append($"OFFSET {SkipRows} ROWS");
if (TakeRows.HasValue)
{
sb.AppendLine();
sb.Append($"FETCH NEXT {TakeRows} ROWS ONLY");
}
}
}
// Return the built query.
return(sb.ToString());
}
