public string getCrossTabSelectPartQuery()
{
string Query = "SELECT ";
// Output Distinct
//if (_distinct)
//{
// Query += "DISTINCT ";
//}
List <Column> columns = new List <Column>();
for (int i = 0; i < _groupByColumns.Count; i++)
{
columns.Add((Column)_groupByColumns.ElementAt(i));
}
columns.Add(_crossTabClause._col);
List <Column> summarizeCols = _crossTabResults.SummarizeColumns;
for (int i = 0; i < summarizeCols.Count; i++)
{
columns.Add((Column)summarizeCols.ElementAt(i));
}
int totalCols = _groupByColumns.Count + 1 + summarizeCols.Count;
for (int i = 0; i < totalCols; i++)
{
Column Col = (Column)columns.ElementAt(i);
Query += SelectQueryBuilder.getColumnPartQuery(Col.Name);
if (Col.AliasName == null || "".Equals(Col.AliasName.Trim()))
{
Query += " AS \"" + CultureInfo.CurrentCulture.TextInfo.ToTitleCase(Col.Name.Substring(Col.Name.IndexOf('.') + 1).Replace('_', ' ')) + "\",";
}
else
{
Query += " AS \"" + Col.AliasName + "\",";
}
}
Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += ' ';
return(Query);
}
public string getQueryPartWithoutSelectForGroupBy()
{
string Query = "";
int totalCols;
string selectPart = getSelectPartQuery(0, -1, out totalCols, true, false);
Query = " FROM (" + selectPart;
// Output table names
if (_selectedTables.Count > 0)
{
Query += " FROM ";
foreach (Table Tab in _selectedTables)
{
Query += Tab.Name + " " + Tab.AliasName + ",";
}
Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += ' ';
}
// Output joins
if (_joins.Count > 0)
{
foreach (JoinClause Clause in _joins)
{
string JoinString = "";
switch (Clause.JoinType)
{
case JoinType.InnerJoin: JoinString = "INNER JOIN"; break;
//case JoinType.OuterJoin: JoinString = "OUTER JOIN"; break;
case JoinType.LeftJoin: JoinString = "LEFT JOIN"; break;
case JoinType.RightJoin: JoinString = "RIGHT JOIN"; break;
}
JoinString += " " + Clause.ToTable.Name + " " + Clause.ToTable.AliasName + " ON ";
//JoinString += WhereStatement.CreateComparisonClause(Clause.FromTable.AliasName + '.' + "`" + Clause.FromColumn+"`", Clause.ComparisonOperator, Clause.ToTable.AliasName + '.' + "`"+Clause.ToColumn+"`");
JoinString += Clause.JoinCondition.BuildWhereStatement();
Query += JoinString + ' ';
}
}
// Output where statement
if (_whereStatement.ClauseLevels > 0)
{
Query += " WHERE " + _whereStatement.BuildWhereStatement();
}
Query += ") grp ";
// Output GroupBy statement
if (_groupByColumns.Count > 0)
{
Query += " GROUP BY ";
foreach (Column Col in _groupByColumns)
{
Query += "`" + Col.Name + "`" + ',';
}
// Add cross Tabulation column as group by in query
if (CrossTabClause.Col != null)
{
Query += "`" + CrossTabClause.Col.Name + "`" + ',';
}
Query = Query.TrimEnd(',');
Query += ' ';
}
// Output having statement
if (_havingStatement.ClauseLevels > 0)
{
// Check if a Group By Clause was set
if (_groupByColumns.Count == 0)
{
throw new Exception("Having statement was set without Group By");
}
Query += " HAVING " + _havingStatement.BuildWhereStatement();
}
// Output OrderBy statement
if (_orderByStatement.Count > 0)
{
Query += " ORDER BY ";
string OrderByClause = "";
foreach (OrderByClause Clause in _orderByStatement)
{
string orderbyFiledStr;
if (_selectedColumns.Count > 0 && _groupByColumns.Count > 0)
{
orderbyFiledStr = "`" + Clause.FieldName + "`";
}
else
{
orderbyFiledStr = SelectQueryBuilder.getColumnPartQuery(Clause.FieldName);
}
switch (Clause.SortOrder)
{
case Sorting.Ascending:
OrderByClause += orderbyFiledStr + " ASC,"; break;
case Sorting.Descending:
OrderByClause += orderbyFiledStr + " DESC,"; break;
}
}
Query += OrderByClause;
Query = Query.TrimEnd(','); // Trim de last AND inserted by foreach loop
Query += ' ';
}
// Return the built query
return(Query);
}
public string getQueryPartWithoutSelect(string sortColumn, bool ascending)
{
string Query = "";
// Output table names
if (_selectedTables.Count > 0)
{
Query += " FROM ";
foreach (Table Tab in _selectedTables)
{
if (Tab is DerivedTable)
{
Query += "(" + ((DerivedTable)Tab).Query + ") " + Tab.AliasName + ",";
}
else if (Tab is Table)
{
if (Tab.SchemaName == null || Tab.SchemaName.Trim().Equals(""))
{
Query += Tab.Name + " " + Tab.AliasName + ",";
}
else
{
Query += Tab.SchemaName + "." + Tab.Name + " " + Tab.AliasName + ",";
}
}
}
Query = Query.TrimEnd(','); // Trim de last comma inserted by foreach loop
Query += ' ';
}
// Output joins
if (_joins.Count > 0)
{
foreach (JoinClause Clause in _joins)
{
string JoinString = "";
switch (Clause.JoinType)
{
case JoinType.InnerJoin: JoinString = "INNER JOIN"; break;
//case JoinType.OuterJoin: JoinString = "OUTER JOIN"; break;
case JoinType.LeftJoin: JoinString = "LEFT JOIN"; break;
case JoinType.RightJoin: JoinString = "RIGHT JOIN"; break;
}
string toTableStr = "";
if (Clause.ToTable is DerivedTable)
{
toTableStr += "(" + ((DerivedTable)Clause.ToTable).Query + ")";
}
else if (Clause.ToTable is Table)
{
if (Clause.ToTable.SchemaName == null || Clause.ToTable.SchemaName.Trim().Equals(""))
{
toTableStr += Clause.ToTable.Name;
}
else
{
toTableStr += Clause.ToTable.SchemaName + "." + Clause.ToTable.Name;
} //toTableStr += Clause.ToTable.Name;
}
JoinString += " " + toTableStr + " " + Clause.ToTable.AliasName + " ON ";
//JoinString += WhereStatement.CreateComparisonClause(Clause.FromTable.AliasName + '.' + "`"+Clause.FromColumn+"`", Clause.ComparisonOperator, Clause.ToTable.AliasName + '.' + "`"+Clause.ToColumn+"`");
JoinString += Clause.JoinCondition.BuildWhereStatement();
Query += JoinString + ' ';
}
}
// Check the computed column and add the joins according ly for GROUP_FIRST, GROUP_LAST etc
Query += getComputeColumnsFromClauseQuery();
// Output where statement
if (_whereStatement.ClauseLevels > 0)
{
Query += " WHERE " + _whereStatement.BuildWhereStatement();
}
// Output GroupBy statement
if (_groupByColumns.Count > 0)
{
Query += " GROUP BY ";
foreach (Column Col in _groupByColumns)
{
Query += SelectQueryBuilder.getColumnPartQuery(Col.Name) + ',';
}
// Add cross Tabulation column as group by in query
if (CrossTabClause.Col != null)
{
Query += SelectQueryBuilder.getColumnPartQuery(CrossTabClause.Col.Name) + ',';
}
Query = Query.TrimEnd(',');
Query += ' ';
}
// Output having statement
if (_havingStatement.ClauseLevels > 0)
{
// Check if a Group By Clause was set
if (_groupByColumns.Count == 0)
{
throw new Exception("Having statement was set without Group By");
}
Query += " HAVING " + _havingStatement.BuildWhereStatement();
}
// Output OrderBy statement
if (_orderByStatement.Count > 0 || sortColumn != null)
{
Query += " ORDER BY ";
string OrderByClause = "";
if (sortColumn != null)
{
if (ascending)
{
OrderByClause = SelectQueryBuilder.getColumnPartQuery(sortColumn) + " ASC,";
}
else
{
OrderByClause = SelectQueryBuilder.getColumnPartQuery(sortColumn) + " DESC,";
}
}
foreach (OrderByClause Clause in _orderByStatement)
{
switch (Clause.SortOrder)
{
case Sorting.Ascending:
OrderByClause += SelectQueryBuilder.getColumnPartQuery(Clause.FieldName) + " ASC,"; break;
case Sorting.Descending:
OrderByClause += SelectQueryBuilder.getColumnPartQuery(Clause.FieldName) + " DESC,"; break;
}
}
Query += OrderByClause;
Query = Query.TrimEnd(','); // Trim de last AND inserted by foreach loop
Query += ' ';
}
// Return the built query
return(Query);
}
