protected override void GetData(SerializationInfo info, StreamingContext context)
{
if (SelectColumns != null)
{
info.AddValue("Columns", SelectColumns.ToArray());
}
else
{
info.AddValue("Columns", null, typeof(SelectColumn[]));
}
info.AddValue("Distinct", Distinct);
info.AddValue("From", FromClause, typeof(FromClause));
info.AddValue("Where", WhereExpression, typeof(SqlExpression));
info.AddValue("Having", HavingExpression, typeof(SqlExpression));
if (GroupBy != null)
{
info.AddValue("GroupBy", GroupBy.ToArray());
}
else
{
info.AddValue("GroupBy", null, typeof(SqlExpression[]));
}
info.AddValue("GroupMax", GroupMax, typeof(ObjectName));
info.AddValue("NextComposite", NextComposite, typeof(SqlQueryExpression));
info.AddValue("CompositeFunction", (int)CompositeFunction);
info.AddValue("CompositeAll", IsCompositeAll);
}
private void GetSelectSQL(List <CustomLine> lines)
{
lines.Add(new CustomLine("/*" + LabelWithComment + "*/", QueryComponent.SELECT));
lines.Add(new CustomLine("SELECT ", QueryComponent.SELECT));
//if there is no top X or an axis is specified (in which case the TopX applies to the PIVOT if any not the axis)
if (!string.IsNullOrWhiteSpace(LimitationSQL))
{
lines.Add(new CustomLine(LimitationSQL, QueryComponent.SELECT));
}
CompileCustomLinesInStageAndAddToList(QueryComponent.SELECT, lines);
CompileCustomLinesInStageAndAddToList(QueryComponent.QueryTimeColumn, lines);
//put in all the selected columns (which are not being skipped because they aren't a part of group by)
foreach (QueryTimeColumn col in SelectColumns.Where(col => !_skipGroupByForThese.Contains(col.IColumn)))
{
if (col.IColumn.HashOnDataRelease)
{
throw new QueryBuildingException("Column " + col.IColumn.GetRuntimeName() + " is marked as HashOnDataRelease and therefore cannot be used as an Aggregate dimension");
}
var line = new CustomLine(col.GetSelectSQL(null, null, _syntaxHelper) + ",", QueryComponent.QueryTimeColumn);
FlagLineBasedOnIcolumn(line, col.IColumn);
//it's the axis dimension tag it with the axis tag
lines.Add(line);
}
//get rid of the trailing comma
lines.Last().Text = lines.Last().Text.TrimEnd('\n', '\r', ',');
}
private void btnColumns_Click(object sender, RoutedEventArgs e)
{
SelectColumns sc = new SelectColumns();
sc.dgResult = dgResult;
sc.ShowDialog();
}
void btnShiTu_Click(object sender, RoutedEventArgs e)
{
try
{
SelectColumns selection = new SelectColumns(_titlelist);
selection.Closed += (object sender1, EventArgs e1) =>
{
SelectColumns quanxian2 = ((SelectColumns)sender1);
bool?result = quanxian2.DialogResult;
if (result.HasValue && result.Value)
{
_titlelist.Clear();
foreach (var item in quanxian2.VisbleList)
{
_titlelist.Add(item.Key, item.Value);
}
TitleInit(_titlelist.Values.ToList());
}
};
selection.ShowDialog();
}
catch (Exception ex)
{
MethodLb.CreateLog(this.GetType().FullName, "btnShiTu_Click", ex.ToString());
}
}
/// <summary>
/// Overload lets you include columns for the purposes of FROM creation but not have them also appear in GROUP BY sections
/// </summary>
/// <param name="col"></param>
/// <param name="includeAsGroupBy"></param>
public void AddColumn(IColumn col, bool includeAsGroupBy)
{
SelectColumns.Add(new QueryTimeColumn(col));
if (!includeAsGroupBy)
{
_skipGroupByForThese.Add(col);
}
}
private IEnumerable <Join> GetRequiredJoins()
{
if (!SelectColumns.Any())
{
return(Joins);
}
return(Joins.Where(IsJoinRequired));
}
public SelectStatement SelectCustom(string statement, string type, string alias, ColumnType columnType = ColumnType.SelectWhereOrderBy, string[] dependentOnAliases = null, bool isVisible = true, int sortOrder = 0)
{
ColumnDef columnDef = new ColumnDef(null, alias, type, 0, false, false, null);
SelectColumn selectColumn = new SelectColumn(null, columnDef, alias, columnType, dependentOnAliases, isVisible, sortOrder, Aggregates.None, null, null, statement);
SelectColumns.Add(selectColumn);
return(this);
}
public QueryBuilder <TEntity> Select <TField>(Expression <Func <TEntity, TField> > field)
{
var memberExpression = field.Body as MemberExpression;
if (!SelectColumns.Contains(memberExpression.Member.Name))
{
SelectColumns.Add(memberExpression.Member.Name);
}
return(this);
}
/// <summary>
/// Converts this SoqlQuery into a string format suitable for use in a SODA call.
/// </summary>
/// <returns>The string representation of this SoqlQuery.</returns>
public override string ToString()
{
var sb = new StringBuilder();
sb.AppendFormat("{0}=", SelectKey);
if (SelectColumns.Length == 1 && SelectColumns[0] == "*")
{
sb.Append(SelectColumns[0]);
}
else
{
//evaluate the provided aliases
var finalColumns = SelectColumns.Zip(SelectColumnAliases, (c, a) => String.Format("{0} AS {1}", c, a)).ToList();
if (SelectColumns.Length > SelectColumnAliases.Length)
{
//some columns were left un-aliased
finalColumns.AddRange(SelectColumns.Skip(SelectColumnAliases.Length));
}
//form the select clause
sb.Append(String.Join(Delimiter, finalColumns));
}
sb.AppendFormat("&{0}={1} {2}", OrderKey, String.Join(Delimiter, OrderByColumns), OrderDirection);
if (!String.IsNullOrEmpty(WhereClause))
{
sb.AppendFormat("&{0}={1}", WhereKey, WhereClause);
}
if (GroupByColumns != null && GroupByColumns.Any())
{
sb.AppendFormat("&{0}={1}", GroupKey, String.Join(Delimiter, GroupByColumns));
}
if (OffsetValue > 0)
{
sb.AppendFormat("&{0}={1}", OffsetKey, OffsetValue);
}
if (LimitValue > 0)
{
sb.AppendFormat("&{0}={1}", LimitKey, LimitValue);
}
if (!String.IsNullOrEmpty(SearchText))
{
sb.AppendFormat("&{0}={1}", SearchKey, SearchText);
}
return(sb.ToString());
}
private void FixOrderBy()
{
int so = 1;
foreach (SelectColumn sc in SelectColumns
.Where(item => item.OrderByIndex != 0)
.OrderBy(item => Math.Abs(item.OrderByIndex)))
{
sc.OrderByIndex = so++ *(sc.OrderByIndex > 0 ? 1 : -1);
}
}
/// <summary>
/// 构建 Select 语句信息
/// </summary>
/// <param name="mainTable">表</param>
public SelectStatement(ITable mainTable)
{
Table = mainTable;
Columns = new SelectColumns();
Joins = new Joins();
Where = new Conditions();
OrderBies = new OrderBies();
GroupBies = new GroupBies();
Having = new Conditions();
}
/// <inheritdoc/>
public void AddColumn(IColumn col)
{
QueryTimeColumn toAdd = new QueryTimeColumn(col);
//if it is new, add it to the list
if (!SelectColumns.Contains(toAdd))
{
SelectColumns.Add(toAdd);
SQLOutOfDate = true;
}
}
/// <inheritdoc cref="ISqlQueryBuilder.AddColumnRange(IColumn[])"/>
/// <param name="columnsToAdd"></param>
/// <param name="includeAsGroupBy">false to add the columns only to the SELECT section of the query (and not GROUP BY)</param>
public void AddColumnRange(IColumn[] columnsToAdd, bool includeAsGroupBy)
{
foreach (IColumn column in columnsToAdd)
{
SelectColumns.Add(new QueryTimeColumn(column));
if (!includeAsGroupBy)
{
_skipGroupByForThese.Add(column);
}
}
}
private bool IsJoinRequired(Join join)
{
//are any of the select query columns for this join Visible or Ordered
bool selectColumns = SelectColumns
.Any(item => (item.Join == join || (item.DependentOnAliases != null && item.DependentOnAliases.Contains(join.Alias))) &&
(item.IsVisible || item.OrderByIndex != 0));
bool whereColumns = WhereColumns.Any(item => item.Join == join);
//are any of the sub joins required
bool subJoinsRequired = Joins
.Where(item => item.ParentJoin == join)
.Any(IsJoinRequired);
return(selectColumns || whereColumns || subJoinsRequired);
}
protected override void GetData(SerializeData data)
{
if (SelectColumns != null)
{
data.SetValue("Columns", SelectColumns.ToArray());
}
data.SetValue("Distinct", Distinct);
data.SetValue("From", FromClause);
data.SetValue("Where", WhereExpression);
data.SetValue("Having", HavingExpression);
if (GroupBy != null)
{
data.SetValue("GroupBy", GroupBy.ToArray());
}
data.SetValue("GroupMax", GroupMax);
data.SetValue("NextComposite", NextComposite);
data.SetValue("CompositeFunction", (int)CompositeFunction);
data.SetValue("CompositeAll", IsCompositeAll);
}
public SelectStatement OrderBy(string alias)
{
SelectColumn selectColumn = SelectColumns.Single(item => item.Alias == alias);
if (Math.Abs(selectColumn.OrderByIndex) == 1)
{
selectColumn.OrderByIndex = -selectColumn.OrderByIndex;
}
else
{
selectColumn.OrderByIndex = 1;
int so = 2;
foreach (SelectColumn sc in SelectColumns
.Where(item => item.Alias != alias && item.OrderByIndex != 0)
.OrderBy(item => Math.Abs(item.OrderByIndex)))
{
sc.OrderByIndex = so++ *(sc.OrderByIndex > 0 ? 1 : -1);
}
}
return(this);
}
public SelectStatement Select(string column, string alias = null, ColumnType columnType = ColumnType.SelectWhereOrderBy, bool isVisible = true, int sortOrder = 0, Aggregates aggregate = Aggregates.None, string isNull = null, SelectStatement optionsSelectStatement = null)
{
GetJoinAndColumnDef(column, (join, columnDef) =>
{
if (String.IsNullOrEmpty(alias))
{
alias = columnDef.Name;
}
ColumnDef aggregateColumnDef = null;
if (aggregate != Aggregates.None)
{
string type = "";
if (aggregate == Aggregates.Count)
{
type = "int";
}
else
{
throw new NotImplementedException();
}
aggregateColumnDef = new ColumnDef(null, alias, type, 0, true, false, null);
}
isVisible = isVisible && ((columnType & ColumnType.Identifier) == ColumnType.Identifier || (columnType & ColumnType.Select) == ColumnType.Select);
SelectColumn selectColumn = new SelectColumn(@join, columnDef, alias, columnType, null, isVisible, sortOrder, aggregate, aggregateColumnDef, isNull, optionsSelectStatement: optionsSelectStatement);
SelectColumns.Add(selectColumn);
});
return(this);
}
/// <summary>
/// Updates .SQL Property, note that this is automatically called when you query .SQL anyway so you do not need to manually call it.
/// </summary>
public void RegenerateSQL()
{
var checkNotifier = new ThrowImmediatelyCheckNotifier();
_sql = "";
//reset the Parameter knowledge
ParameterManager.ClearNonGlobals();
#region Setup to output the query, where we figure out all the joins etc
//reset everything
SelectColumns.Sort();
//work out all the filters
Filters = SqlQueryBuilderHelper.GetAllFiltersUsedInContainerTreeRecursively(RootFilterContainer);
TableInfo primary;
TablesUsedInQuery = SqlQueryBuilderHelper.GetTablesUsedInQuery(this, out primary, _forceJoinsToTheseTables);
//force join to any TableInfos that would not be normally joined to but the user wants to anyway e.g. if theres WHERE sql that references them but no columns
if (_forceJoinsToTheseTables != null)
{
foreach (var force in _forceJoinsToTheseTables)
{
if (!TablesUsedInQuery.Contains(force))
{
TablesUsedInQuery.Add(force);
}
}
}
this.PrimaryExtractionTable = primary;
SqlQueryBuilderHelper.FindLookups(this);
JoinsUsedInQuery = SqlQueryBuilderHelper.FindRequiredJoins(this);
//deal with case when there are no tables in the query or there are only lookup descriptions in the query
if (TablesUsedInQuery.Count == 0)
{
throw new Exception("There are no TablesUsedInQuery in this dataset");
}
_syntaxHelper = SqlQueryBuilderHelper.GetSyntaxHelper(TablesUsedInQuery);
if (TopX != -1)
{
SqlQueryBuilderHelper.HandleTopX(this, _syntaxHelper, TopX);
}
else
{
SqlQueryBuilderHelper.ClearTopX(this);
}
//declare parameters
ParameterManager.AddParametersFor(Filters);
#endregion
/////////////////////////////////////////////Assemble Query///////////////////////////////
#region Preamble (including variable declarations/initializations)
//assemble the query - never use Environment.Newline, use TakeNewLine() so that QueryBuilder knows what line its got up to
string toReturn = "";
foreach (ISqlParameter parameter in ParameterManager.GetFinalResolvedParametersList())
{
//if the parameter is one that needs to be told what the query syntax helper is e.g. if it's a global parameter designed to work on multiple datasets
var needsToldTheSyntaxHelper = parameter as IInjectKnown <IQuerySyntaxHelper>;
if (needsToldTheSyntaxHelper != null)
{
needsToldTheSyntaxHelper.InjectKnown(_syntaxHelper);
}
if (CheckSyntax)
{
parameter.Check(checkNotifier);
}
toReturn += GetParameterDeclarationSQL(parameter);
}
//add user custom Parameter lines
toReturn = AppendCustomLines(toReturn, QueryComponent.VariableDeclaration);
#endregion
#region Select (including all IColumns)
toReturn += Environment.NewLine;
toReturn += "SELECT " + LimitationSQL + Environment.NewLine;
toReturn = AppendCustomLines(toReturn, QueryComponent.SELECT);
toReturn += Environment.NewLine;
toReturn = AppendCustomLines(toReturn, QueryComponent.QueryTimeColumn);
for (int i = 0; i < SelectColumns.Count; i++)
{
//output each of the ExtractionInformations that the user requested and record the line number for posterity
string columnAsSql = SelectColumns[i].GetSelectSQL(_hashingAlgorithm, _salt, _syntaxHelper);
//there is another one coming
if (i + 1 < SelectColumns.Count)
{
columnAsSql += ",";
}
toReturn += columnAsSql + Environment.NewLine;
}
#endregion
//work out basic JOINS Sql
toReturn += SqlQueryBuilderHelper.GetFROMSQL(this);
//add user custom JOIN lines
toReturn = AppendCustomLines(toReturn, QueryComponent.JoinInfoJoin);
#region Filters (WHERE)
toReturn += SqlQueryBuilderHelper.GetWHERESQL(this);
toReturn = AppendCustomLines(toReturn, QueryComponent.WHERE);
toReturn = AppendCustomLines(toReturn, QueryComponent.Postfix);
_sql = toReturn;
SQLOutOfDate = false;
#endregion
}
internal override void AppendTo(SqlStringBuilder builder)
{
builder.Append("SELECT ");
if (Distinct)
{
builder.Append("DISTINCT ");
}
var columns = SelectColumns.ToArray();
var sz = columns.Length;
for (int i = 0; i < sz; i++)
{
columns[i].AppendTo(builder);
if (i < sz - 1)
{
builder.Append(", ");
}
}
builder.Append(" ");
if (FromClause != null)
{
FromClause.AppendTo(builder);
}
if (WhereExpression != null)
{
builder.Append(" WHERE ");
WhereExpression.AppendTo(builder);
}
if (GroupBy != null)
{
var groupBy = GroupBy.ToList();
builder.Append(" GROUP BY ");
for (int i = 0; i < groupBy.Count; i++)
{
groupBy[i].AppendTo(builder);
if (i < groupBy.Count - 1)
{
builder.Append(", ");
}
}
if (HavingExpression != null)
{
builder.Append(" HVAING ");
HavingExpression.AppendTo(builder);
}
}
if (GroupMax != null)
{
builder.Append(" GROUP MAX ");
GroupMax.AppendTo(builder);
}
// TODO: COMPOSITE ...
}
public virtual string TransformText()
{
#line 4 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.tt"
if (DbmsPlatform == "SQL Server")
{
#line default
#line hidden
this.Write("IF OBJECT_ID(N\'");
#line 1 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(ObjectView));
#line default
#line hidden
this.Write("\', N\'V\') IS NULL\r\n\tEXECUTE (\'CREATE VIEW ");
#line 2 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(ObjectView));
#line default
#line hidden
this.Write(" AS SELECT NULL AS CREATE_OR_REPLACE\');\r\nGO\r\n\r\nALTER VIEW ");
#line 5 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(ObjectView));
#line default
#line hidden
this.Write(" AS\r\n-- This code was generated by ");
#line 6 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(TemplateName));
#line default
#line hidden
this.Write(" @ ");
#line 6 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DateTime.Now.ToString()));
#line default
#line hidden
this.Write("\r\nSELECT\r\n\tD.");
#line 8 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DateColumn));
#line default
#line hidden
#line 8 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SelectColumns.Select(c => "S." + c).InsertRight()));
#line default
#line hidden
this.Write("\r\nFROM\r\n\t");
#line 10 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceView));
#line default
#line hidden
this.Write("\tS,\r\n\t");
#line 11 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyView));
#line default
#line hidden
this.Write("\tD\r\nWHERE\r\n\t(\r\n\t\tS.");
#line 14 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(RangeEndDateColumn));
#line default
#line hidden
this.Write(" ");
#line 14 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(IsEndDateNext ? ">" : ">="));
#line default
#line hidden
this.Write(" D.");
#line 14 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DateColumn));
#line default
#line hidden
this.Write("\r\n");
#line 15 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
if (IsEndDateNullable)
{
#line default
#line hidden
this.Write("\t\tOR S.");
#line 16 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(RangeEndDateColumn));
#line default
#line hidden
this.Write(" IS NULL\r\n");
#line 17 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
}
#line default
#line hidden
this.Write("\t)\r\n\tAND S.");
#line 19 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(RangeStartDateColumn));
#line default
#line hidden
this.Write(" <= D.");
#line 19 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DateColumn));
#line default
#line hidden
this.Write(@"
;
GO
----------------------------------------------------------------------------------------------------
--
-- Copyright 2013 Abel Cheng
-- This source code is subject to terms and conditions of the Apache License, Version 2.0.
-- See http://www.apache.org/licenses/LICENSE-2.0.
-- All other rights reserved.
-- You must not remove this notice, or any other, from this software.
--
-- Original Author: Abel Cheng <*****@*****.**>
-- Created Date: May 15, 2013, 11:27:30 PM
-- Primary Host: http://t4sql.codeplex.com
-- Change Log:
-- Author Date Comment
--
--
--
--
-- (Keep code clean)
--
----------------------------------------------------------------------------------------------------
");
this.Write("\r\n");
#line 9 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.tt"
}
else
if (DbmsPlatform == "Oracle")
{
#line default
#line hidden
this.Write("CREATE OR REPLACE VIEW ");
#line 1 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(ObjectView));
#line default
#line hidden
this.Write(" AS\r\nSELECT\r\n\t-- This code was generated by ");
#line 3 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(TemplateName));
#line default
#line hidden
this.Write(" @ ");
#line 3 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DateTime.Now.ToString()));
#line default
#line hidden
this.Write("\r\n\tD.");
#line 4 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DateColumn));
#line default
#line hidden
#line 4 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SelectColumns.Select(c => "S." + c).InsertRight()));
#line default
#line hidden
this.Write("\r\nFROM\r\n\t");
#line 6 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceView));
#line default
#line hidden
this.Write("\tS,\r\n\t");
#line 7 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyView));
#line default
#line hidden
this.Write("\tD\r\nWHERE\r\n\t(\r\n\t\tS.");
#line 10 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(RangeEndDateColumn));
#line default
#line hidden
this.Write(" ");
#line 10 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(IsEndDateNext ? ">" : ">="));
#line default
#line hidden
this.Write(" D.");
#line 10 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DateColumn));
#line default
#line hidden
this.Write("\r\n");
#line 11 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
if (IsEndDateNullable)
{
#line default
#line hidden
this.Write("\t\tOR S.");
#line 12 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(RangeEndDateColumn));
#line default
#line hidden
this.Write(" IS NULL\r\n");
#line 13 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
}
#line default
#line hidden
this.Write("\t)\r\n\tAND S.");
#line 15 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(RangeStartDateColumn));
#line default
#line hidden
this.Write(" <= D.");
#line 15 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DateColumn));
#line default
#line hidden
this.Write(@"
WITH READ ONLY;
----------------------------------------------------------------------------------------------------
--
-- Copyright 2013 Abel Cheng
-- This source code is subject to terms and conditions of the Apache License, Version 2.0.
-- See http://www.apache.org/licenses/LICENSE-2.0.
-- All other rights reserved.
-- You must not remove this notice, or any other, from this software.
--
-- Original Author: Abel Cheng <*****@*****.**>
-- Created Date: May 15, 2013, 11:27:30 PM
-- Primary Host: http://t4sql.codeplex.com
-- Change Log:
-- Author Date Comment
--
--
--
--
-- (Keep code clean)
--
----------------------------------------------------------------------------------------------------
");
this.Write("\r\n");
#line 15 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.tt"
}
else
{
Error("T4SQL.Date.VTimeRangesToSeries is not implemented for " + DbmsPlatform);
}
#line default
#line hidden
#line 21 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimeRangesToSeries.tt"
/*
* Before building the project or checking in code, if any included file changes,
* you should re-transform the including template in the solution by:
*
* Right-click one or more files in Solution Explorer and then click Run Custom Tool.
* Use this method to transform a selected set of templates.
* or
* Click Transform All Templates in the Solution Explorer toolbar.
* This will transform all the templates in the Visual Studio solution.
* or
* Installed Visual Studio Visualization and Modeling SDK
* http://archive.msdn.microsoft.com/vsvmsdk,
* And setup all the templates transformed automatically:
* http://msdn.microsoft.com/en-us/library/dd820620.aspx#Regenerating
* http://msdn.microsoft.com/en-us/library/ee847423.aspx
* http://msdn.microsoft.com/en-us/library/ff521399.aspx
*/
#line default
#line hidden
return(this.GenerationEnvironment.ToString());
}
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 void ValidateDimensions()
{
//axis but no pivot
if (_axis != null && _pivotDimension == null && SelectColumns.Count != 2)
{
throw new QueryBuildingException("You must have two columns in an AggregateConfiguration that contains an axis. These must be the axis column and the count/sum column. Your query had " + SelectColumns.Count + " (" + string.Join(",", SelectColumns.Select(c => "'" + c.IColumn.ToString() + "'")) + ")");
}
//axis and pivot
if (_axis != null && _pivotDimension != null && SelectColumns.Count != 3)
{
throw new QueryBuildingException("You must have three columns in an AggregateConfiguration that contains a pivot. These must be the axis column, the pivot column and the count/sum column. Your query had " + SelectColumns.Count + " (" + string.Join(",", SelectColumns.Select(c => "'" + c.IColumn.ToString() + "'")) + ")");
}
}
/// <summary>
/// Populates _sql (SQL property) and resolves all parameters, filters containers etc. Basically Finalizes this query builder
/// </summary>
public void RegenerateSQL()
{
SelectColumns.Sort();
//things we discover below, set them all to default values again
_pivotDimension = null;
_axisAppliesToDimension = null;
_axis = null;
_isCohortIdentificationAggregate = false;
ParameterManager.ClearNonGlobals();
if (_queryLevelParameterProvider != null)
{
ParameterManager.AddParametersFor(_queryLevelParameterProvider, ParameterLevel.QueryLevel);
}
TableInfo primary;
TablesUsedInQuery = SqlQueryBuilderHelper.GetTablesUsedInQuery(this, out primary, _forceJoinsToTheseTables);
var tables = _forceJoinsToTheseTables != null
? TablesUsedInQuery.Union(_forceJoinsToTheseTables).ToList()
: TablesUsedInQuery;
if (!tables.Any())
{
throw new QueryBuildingException("No tables could be identified for the query. Try adding a column or a force join");
}
//get the database language syntax based on the tables used in the query
_syntaxHelper = SqlQueryBuilderHelper.GetSyntaxHelper(tables);
//tell the count column what language it is
if (_countColumn != null)
{
_isCohortIdentificationAggregate = _aggregateConfigurationIfAny != null && _aggregateConfigurationIfAny.IsCohortIdentificationAggregate;
//if it is not a cic aggregate then make sure it has an alias e.g. count(*) AS MyCount. cic aggregates take extreme liberties with this field like passing in 'distinct chi' and '*' and other wacky stuff that is so not cool
_countColumn.SetQuerySyntaxHelper(_syntaxHelper, !_isCohortIdentificationAggregate);
}
IAggregateHelper aggregateHelper = _syntaxHelper.AggregateHelper;
if (_pivotID != -1)
{
try
{
_pivotDimension = SelectColumns.Single(
qtc => qtc.IColumn is AggregateDimension
&&
((AggregateDimension)qtc.IColumn).ID == _pivotID);
}
catch (Exception e)
{
throw new QueryBuildingException("Problem occurred when trying to find PivotDimension ID " + _pivotID + " in SelectColumns list", e);
}
}
foreach (AggregateDimension dimension in SelectColumns.Select(c => c.IColumn).Where(e => e is AggregateDimension))
{
var availableAxis = dimension.AggregateContinuousDateAxis;
if (availableAxis != null)
{
if (_axis != null)
{
throw new QueryBuildingException(
"Multiple dimensions have an AggregateContinuousDateAxis within the same configuration (Dimensions " + _axisAppliesToDimension.GetRuntimeName() + " and " + dimension.GetRuntimeName() + ")");
}
else
{
_axis = availableAxis;
_axisAppliesToDimension = dimension;
}
}
}
if (_pivotDimension != null)
{
if (_pivotDimension.IColumn == _axisAppliesToDimension)
{
throw new QueryBuildingException("Column " + _pivotDimension.IColumn + " is both a PIVOT and has an AXIS configured on it, you cannot have both.");
}
}
//work out all the filters
Filters = SqlQueryBuilderHelper.GetAllFiltersUsedInContainerTreeRecursively(RootFilterContainer);
//tell the manager about them
ParameterManager.AddParametersFor(Filters);
if (AggregateTopX != null)
{
SqlQueryBuilderHelper.HandleTopX(this, _syntaxHelper, AggregateTopX.TopX);
}
else
{
SqlQueryBuilderHelper.ClearTopX(this);
}
//if user wants to force join to some other tables that don't appear in the SELECT list, who are we to stop him!
if (_forceJoinsToTheseTables != null)
{
foreach (TableInfo t in _forceJoinsToTheseTables)
{
if (!TablesUsedInQuery.Contains(t))
{
TablesUsedInQuery.Add(t);
ParameterManager.AddParametersFor(t);
}
//if user has force joined to a primary extraction table
if (t.IsPrimaryExtractionTable)
{
if (primary == null) //we don't currently know the primary (i.e. none of the SELECT columns were from primary tables so use this table as primary)
{
primary = t;
}
else if (primary.ID == t.ID) //we know the primary already but it is the same table so thats fine
{
continue;
}
else
{
//this isn't fine
throw new QueryBuildingException("You chose to FORCE a join to table " + t + " which is marked IsPrimaryExtractionTable but you have also selected a column called " + primary + " which is also an IsPrimaryExtractionTable (cannot have 2 different primary extraction tables)");
}
}
}
}
this.PrimaryExtractionTable = primary;
SqlQueryBuilderHelper.FindLookups(this);
JoinsUsedInQuery = SqlQueryBuilderHelper.FindRequiredJoins(this);
var queryLines = new List <CustomLine>();
_sql = "";
ValidateDimensions();
//assuming we were not told to ignore the writing out of parameters!
if (!DoNotWriteOutParameters)
{
foreach (ISqlParameter parameter in ParameterManager.GetFinalResolvedParametersList())
{
queryLines.Add(new CustomLine(QueryBuilder.GetParameterDeclarationSQL(parameter), QueryComponent.VariableDeclaration));
}
}
CompileCustomLinesInStageAndAddToList(QueryComponent.VariableDeclaration, queryLines);
//put the name in as SQL comments followed by the SQL e.g. the name of an AggregateConfiguration or whatever
GetSelectSQL(queryLines);
queryLines.Add(new CustomLine(SqlQueryBuilderHelper.GetFROMSQL(this), QueryComponent.FROM));
CompileCustomLinesInStageAndAddToList(QueryComponent.JoinInfoJoin, queryLines);
queryLines.Add(new CustomLine(SqlQueryBuilderHelper.GetWHERESQL(this), QueryComponent.WHERE));
CompileCustomLinesInStageAndAddToList(QueryComponent.WHERE, queryLines);
GetGroupBySQL(queryLines, aggregateHelper);
queryLines = queryLines.Where(l => !string.IsNullOrWhiteSpace(l.Text)).ToList();
_sql = aggregateHelper.BuildAggregate(queryLines, _axis);
}
public ActionResult Create(FormCollection collection)
{
try
{
QueryMaster query = new QueryMaster();
query.QueryText = collection[1];
// TODO: Add insert logic here
query.customerid = Convert.ToInt32(collection[2]);
context.Query.Add(query);
context.SaveChanges();
Parser parser = new Parser();
List <Parser.outputstruct> tablelist = Parser.GetTableNamesFromQueryString(query.QueryText);
foreach (Parser.outputstruct n in tablelist)
{
TableList table = new TableList();
table.TableName = n.Table;
table.owner = n.Owner;
table.AliasName = n.Alias;
table.queryID = query.ID;
context.Tables.Add(table);
}
context.SaveChanges();
List <Parser.columnstruct> columnlist = Parser.FindColumns(query.QueryText);
foreach (Parser.columnstruct n in columnlist)
{
SelectColumns columns = new SelectColumns();
var tbl =
from c in context.Tables
where c.queryID == query.ID &&
(c.AliasName.Replace("[", "").Replace("]", "") == n.Alias || c.TableName.Replace("[", "").Replace("]", "") == n.Alias)
select c.TableName;
if (tbl.Count() > 0)
{
columns.QueryID = query.ID;
columns.TableName = tbl.First().ToString();
columns.ColumnName = n.Column;
context.columnlist.Add(columns);
}
}
context.SaveChanges();
List <Parser.wherestruct> whereclauseList = Parser.GetFilterCriteriaFromQueryString(query.QueryText, tablelist);
foreach (Parser.wherestruct n in whereclauseList)
{
WhereClause whereclauseClass = new WhereClause();
whereclauseClass.ColumnName = n.Column;
whereclauseClass.comparison_operator = n.comparison_operator;
whereclauseClass.comparison_value = n.comparison_value;
whereclauseClass.TableName = n.Table;
whereclauseClass.QueryID = query.ID;
whereclauseClass.function_string = n.function_string;
whereclauseClass.function_name = n.function_name;
context.whereclause.Add(whereclauseClass);
}
context.SaveChanges();
return(RedirectToAction("Index"));
}
catch (Exception ex)
{
return(View());
}
}
public virtual string TransformText()
{
#line 4 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.tt"
if (DbmsPlatform == "SQL Server")
{
#line default
#line hidden
this.Write("IF OBJECT_ID(N\'");
#line 1 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(ObjectView));
#line default
#line hidden
this.Write("\', N\'V\') IS NULL\r\n\tEXECUTE (\'CREATE VIEW ");
#line 2 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(ObjectView));
#line default
#line hidden
this.Write(" AS SELECT NULL AS CREATE_OR_REPLACE\');\r\nGO\r\n\r\nALTER VIEW ");
#line 5 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(ObjectView));
#line default
#line hidden
this.Write(" AS\r\n-- This code was generated by ");
#line 6 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(TemplateName));
#line default
#line hidden
this.Write(" @ ");
#line 6 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DateTime.Now.ToString()));
#line default
#line hidden
this.Write("\r\n");
#line 7 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
if (DbmsVersion > new Version(11, 0)) // SQL Server 2012
{
#line default
#line hidden
this.Write("SELECT\r\n\tD.");
#line 12 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyDateColumn));
#line default
#line hidden
#line 12 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SelectColumns.Select(c => "S." + c).InsertRight()));
#line default
#line hidden
this.Write("\r\nFROM\r\n\t(\r\n\t\tSELECT\r\n\t\t\t");
#line 16 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SelectColumns.InsertLeft()));
#line default
#line hidden
this.Write("\r\n\t\t\t");
#line 17 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceDateColumn));
#line default
#line hidden
this.Write("\t\tAS START$DATE,\r\n\t\t\tLEAD(");
#line 18 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceDateColumn));
#line default
#line hidden
this.Write(", 1, CAST(\'2999-12-31\' AS DATE)) OVER (PARTITION BY ");
#line 18 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(Key_Columns));
#line default
#line hidden
this.Write(" ORDER BY ");
#line 18 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceDateColumn));
#line default
#line hidden
this.Write(")\r\n\t\t\t\t\t\t\t\t\t\tAS EXPIRE$DATE\r\n\t\tFROM\r\n\t\t\t");
#line 21 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceView));
#line default
#line hidden
this.Write("\r\n\t)\tS,\r\n\t");
#line 23 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyView));
#line default
#line hidden
this.Write("\tD\r\nWHERE\r\n\t\tS.EXPIRE$DATE > D.");
#line 25 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyDateColumn));
#line default
#line hidden
this.Write("\r\n\tAND S.START$DATE <= D.");
#line 26 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyDateColumn));
#line default
#line hidden
this.Write("\r\n");
#line 27 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
}
else // SQL Server 2008, 2005
{
#line default
#line hidden
this.Write("WITH TR AS\r\n(\r\n\tSELECT\r\n\t\t*,\r\n\t\tROW_NUMBER() OVER (PARTITION BY ");
#line 36 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(Key_Columns));
#line default
#line hidden
this.Write(" ORDER BY ");
#line 36 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceDateColumn));
#line default
#line hidden
this.Write(") AS ROW$NUMBER\r\n\tFROM\r\n\t\t");
#line 38 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceView));
#line default
#line hidden
this.Write("\r\n)\r\nSELECT\r\n\tD.");
#line 41 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyDateColumn));
#line default
#line hidden
#line 41 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SelectColumns.Select(c => "S." + c).InsertRight()));
#line default
#line hidden
this.Write("\r\nFROM\r\n\t(\r\n\t\tSELECT\r\n\t\t\t");
#line 45 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SelectColumns.Select(c => "t1." + c).InsertLeft()));
#line default
#line hidden
this.Write("\r\n\t\t\tt1.");
#line 46 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceDateColumn));
#line default
#line hidden
this.Write("\t\t\t\t\t\t\t\t\t\t\tAS START$DATE,\r\n\t\t\tISNULL(t2.");
#line 47 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceDateColumn));
#line default
#line hidden
this.Write(", CAST(\'2999-12-31\' AS DATE))\t\tAS EXPIRE$DATE\r\n\t\tFROM\r\n\t\t\tTR t1 LEFT OUTER JOIN\r\n" +
"\t\t\tTR t2 ON (");
#line 50 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(String.Join(" AND ", KeyColumns.Select(k => string.Format(@"t1.{0} = t2.{0}", k)))));
#line default
#line hidden
this.Write("\r\n\t\t\t\tAND t1.ROW$NUMBER + 1 = t2.ROW$NUMBER)\r\n\t)\tS,\r\n\t");
#line 53 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyView));
#line default
#line hidden
this.Write("\tD\r\nWHERE\r\n\t\tS.EXPIRE$DATE > D.");
#line 55 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyDateColumn));
#line default
#line hidden
this.Write("\r\n\tAND S.START$DATE <= D.");
#line 56 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyDateColumn));
#line default
#line hidden
this.Write("\r\n");
#line 57 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.SqlServer.sql"
}
#line default
#line hidden
this.Write(@";
GO
----------------------------------------------------------------------------------------------------
--
-- Copyright 2013 Abel Cheng
-- This source code is subject to terms and conditions of the Apache License, Version 2.0.
-- See http://www.apache.org/licenses/LICENSE-2.0.
-- All other rights reserved.
-- You must not remove this notice, or any other, from this software.
--
-- Original Author: Abel Cheng <*****@*****.**>
-- Created Date: May 15, 2013, 11:25:04 PM
-- Primary Host: http://t4sql.codeplex.com
-- Change Log:
-- Author Date Comment
--
--
--
--
-- (Keep code clean)
--
----------------------------------------------------------------------------------------------------
");
this.Write("\r\n");
#line 9 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.tt"
}
else
if (DbmsPlatform == "Oracle")
{
#line default
#line hidden
this.Write("CREATE OR REPLACE VIEW ");
#line 1 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(ObjectView));
#line default
#line hidden
this.Write(" AS\r\nSELECT\r\n\t-- This code was generated by ");
#line 3 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(TemplateName));
#line default
#line hidden
this.Write(" @ ");
#line 3 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DateTime.Now.ToString()));
#line default
#line hidden
this.Write("\r\n\tD.");
#line 4 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyDateColumn));
#line default
#line hidden
#line 4 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SelectColumns.Select(c => "S." + c).InsertRight()));
#line default
#line hidden
this.Write("\r\nFROM\r\n\t(\r\n\t\tSELECT\r\n\t\t\t");
#line 8 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SelectColumns.InsertLeft()));
#line default
#line hidden
this.Write("\r\n\t\t\t");
#line 9 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceDateColumn));
#line default
#line hidden
this.Write("\t\tAS START$DATE,\r\n\t\t\tLEAD(");
#line 10 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceDateColumn));
#line default
#line hidden
this.Write(", 1, TO_DATE(\'2999-12-31\', \'YYYY-MM-DD\')) OVER (PARTITION BY ");
#line 10 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(Key_Columns));
#line default
#line hidden
this.Write(" ORDER BY ");
#line 10 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceDateColumn));
#line default
#line hidden
this.Write(")\r\n\t\t\t\t\t\t\t\t\t\tAS EXPIRE$DATE\r\n\t\tFROM\r\n\t\t\t");
#line 13 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(SourceView));
#line default
#line hidden
this.Write("\r\n\t)\tS,\r\n\t");
#line 15 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyView));
#line default
#line hidden
this.Write("\tD\r\nWHERE\r\n\t\tS.EXPIRE$DATE > D.");
#line 17 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyDateColumn));
#line default
#line hidden
this.Write("\r\n\tAND S.START$DATE <= D.");
#line 18 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.Oracle.sql"
this.Write(this.ToStringHelper.ToStringWithCulture(DailyDateColumn));
#line default
#line hidden
this.Write(@"
WITH READ ONLY;
----------------------------------------------------------------------------------------------------
--
-- Copyright 2013 Abel Cheng
-- This source code is subject to terms and conditions of the Apache License, Version 2.0.
-- See http://www.apache.org/licenses/LICENSE-2.0.
-- All other rights reserved.
-- You must not remove this notice, or any other, from this software.
--
-- Original Author: Abel Cheng <*****@*****.**>
-- Created Date: May 15, 2013, 11:25:04 PM
-- Primary Host: http://t4sql.codeplex.com
-- Change Log:
-- Author Date Comment
--
--
--
--
-- (Keep code clean)
--
----------------------------------------------------------------------------------------------------
");
this.Write("\r\n");
#line 15 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.tt"
}
else
{
Error("T4SQL.Date.VTimePointsToSeries is not implemented for " + DbmsPlatform);
}
#line default
#line hidden
#line 21 "E:\Projects\T4SQL\T4SQLTemplateLibrary\T4Templates\Date\VTimePointsToSeries.tt"
/*
* Before building the project or checking in code, if any included file changes,
* you should re-transform the including template in the solution by:
*
* Right-click one or more files in Solution Explorer and then click Run Custom Tool.
* Use this method to transform a selected set of templates.
* or
* Click Transform All Templates in the Solution Explorer toolbar.
* This will transform all the templates in the Visual Studio solution.
* or
* Installed Visual Studio Visualization and Modeling SDK
* http://archive.msdn.microsoft.com/vsvmsdk,
* And setup all the templates transformed automatically:
* http://msdn.microsoft.com/en-us/library/dd820620.aspx#Regenerating
* http://msdn.microsoft.com/en-us/library/ee847423.aspx
* http://msdn.microsoft.com/en-us/library/ff521399.aspx
*/
#line default
#line hidden
return(this.GenerationEnvironment.ToString());
}
