private void ResolveWH(List <Where> whereToWork, string sufix, string operation, string value, string logical)
{
if (sufix == string.Empty && value == string.Empty)
{
return;
}
if (operation == string.Empty)
{
operation = "=";
value = sufix;
sufix = string.Empty;
}
if (logical == string.Empty)
{
logical = "AND";
}
Where _where = new Where();
_where.Left = _selectedColumn;
_where.LeftSufix = sufix;
_where.Operation = OperationFX.StringToOperation(operation);
_where.Right = value;
_where.Junction = logical;
whereToWork.Add(_where);
}
private void labelsWithJoinOperation_Click(object sender, EventArgs e)
{
labelCurrent.Text = ((Label)sender).Text;
labelCurrent.Font = new Font(((Label)sender).Font.FontFamily, ((Label)sender).Font.Size);
_join.Operation = OperationFX.StringToOperation(labelCurrent.Text);
if (JoinChanged != null)
{
JoinChanged(this, new JoinEventArgs(_join, JoinEventAction.Change));
}
this.Collapse();
}
/// <summary>
/// Verifica se é um join visual e em caso positivo adiciona ele na tela
/// </summary>
/// <param name="joinPossible">Linha de Join no Where</param>
/// <returns>Se adicionou visualmente retorna true senão false</returns>
private bool IsJoinAndCanAddIt(List <Database.SelectAnalyzer.Consulta.WherePiece> joinPossible)
{
int _openPar = 0;
// ignora qualquer parenteses aberto antes dos comandos de ligação
while (joinPossible[_openPar] is Database.SelectAnalyzer.Consulta.WhereOpenParentesesPiece)
{
_openPar++;
}
// Se fecha com a juncao ignora a junção. Depois ignora os parêteses que fecham
int _closePar = joinPossible.Count - 1;
if (_closePar < 2)
{
return(false);
}
if (joinPossible[_closePar] is Database.SelectAnalyzer.Consulta.WhereJunctionPiece)
{
_closePar--;
}
while (joinPossible[_closePar] is Database.SelectAnalyzer.Consulta.WhereCloseParentesesPiece)
{
_closePar--;
}
// se ter três comandos sendo duas colunas e uma ligação lógica
if (_closePar - _openPar == 2)
{
if (joinPossible[_openPar] is Database.SelectAnalyzer.Consulta.WhereColumnPiece &&
joinPossible[_closePar] is Database.SelectAnalyzer.Consulta.WhereColumnPiece &&
joinPossible[_openPar + 1] is Database.SelectAnalyzer.Consulta.WhereLogicalOperatorPiece)
{
Column _left = _query.FindColumn(joinPossible[_openPar].Syntax);
Column _right = _query.FindColumn(joinPossible[_closePar].Syntax);
// Cria o Join e adiciona ele na tela
Join _join = new Join(_left, _right);
_join.Operation = OperationFX.StringToOperation(joinPossible[_openPar + 1].Syntax);
this.AddVisualJoin(_join);
return(true);
}
}
return(false);
}
/// <summary>
/// Edição avançada de Where
/// </summary>
/// <param name="sender">Grade</param>
/// <param name="e">Argumento</param>
private void dataGridViewWhere_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
DataGridView dgv = (DataGridView)sender;
Where _w;
// Se é reg novo
if (_query.Wheres.Count < e.RowIndex + 1)
{
_w = new Where();
_query.Wheres.Add(_w);
}
else
{
_w = _query.Wheres[e.RowIndex];
}
switch (e.ColumnIndex)
{
case 0:
Column _c = _query.FindColumn(dgv.CurrentCell.Value.ToString()); // busca a coluna
if (_c != null)
{
_w.Left = _c;
dgv.CurrentRow.Cells[1].Value = "=";
dgv.CurrentRow.Cells[3].Value = "AND";
}
break;
case 1:
_w.Operation = OperationFX.StringToOperation(dgv.CurrentCell.Value.ToString());
break;
case 2:
_w.Right = dgv.CurrentCell.Value.ToString();
break;
case 3:
_w.Junction = dgv.CurrentCell.Value.ToString();
break;
}
this.RenderSQL();
}
/// <summary>
/// Carrega os dados do TreeView
/// </summary>
private void LoadTreeView()
{
treeViewSQL.SuspendLayout();
treeViewSQL.Nodes.Clear();
treeViewSQL.Nodes.Add("SQL");
treeViewSQL.Nodes[0].Nodes.Add("SELECT");
foreach (Column _col in _query.Columns)
{
treeViewSQL.Nodes[0].Nodes[0].Nodes.Add(_col.FullName);
}
treeViewSQL.Nodes[0].Nodes.Add("FROM");
foreach (Table _tab in _query.Tables)
{
treeViewSQL.Nodes[0].Nodes[1].Nodes.Add(_tab.FullName);
}
treeViewSQL.Nodes[0].Nodes.Add("JOIN");
foreach (Join _join in _query.Joins)
{
treeViewSQL.Nodes[0].Nodes[2].Nodes.Add(string.Format("{0} {1} {2}", _join.Left.SchemaName, OperationFX.OperationToString(_join.Operation), _join.Right.SchemaName));
}
treeViewSQL.Nodes[0].Nodes.Add("WHERE");
foreach (Where _where in _query.Wheres)
{
treeViewSQL.Nodes[0].Nodes[3].Nodes.Add(_where.ToString());
}
treeViewSQL.Nodes[0].Nodes.Add("GROUP BY");
foreach (Column _col in _query.Groups)
{
treeViewSQL.Nodes[0].Nodes[4].Nodes.Add(_col.SchemaName);
}
treeViewSQL.Nodes[0].Nodes.Add("HAVING");
foreach (Where _hav in _query.Havings)
{
treeViewSQL.Nodes[0].Nodes[5].Nodes.Add(_hav.ToString());
}
treeViewSQL.Nodes[0].Nodes.Add("ORDER BY");
foreach (Order _order in _query.Orders)
{
treeViewSQL.Nodes[0].Nodes[6].Nodes.Add(string.Format("{0} {1}", _order.Column.SchemaName, (_order.DescendingOrder)?"DESC":"ASC"));
}
treeViewSQL.ExpandAll();
treeViewSQL.PerformLayout();
}
/// <summary>
/// Carrega visual a Sintaxe SQL
/// </summary>
/// <param name="syntax"></param>
public void LoadSyntax(string syntax)
{
// Sintaxes identicas não procisa fazer nada
if (syntax == this.richTextBoxSQL.Text)
{
return;
}
if (syntax.EndsWith(";"))
{
syntax = syntax.Remove(syntax.Length - 1);
}
Database.SelectAnalyzer _sql = new Database.SelectAnalyzer(syntax);
foreach (Database.SelectAnalyzer.Consulta consulta in _sql.Consultas)
{
if (consulta.Tipo == Database.SelectAnalyzer.TipoConsulta.Uniao)
{
throw new Error.Excessao(197);
}
}
if (_sql.Consultas.Length == 0)
{
this.Clear();
if (syntax.Trim() == string.Empty)
{
return;
}
else
{
throw new Error.Excessao(197);
}
}
Database.SelectAnalyzer.Consulta _consulta = _sql.Consultas[0];
// Já tem uma sintaxe anterior
if (this.richTextBoxSQL.Text != string.Empty)
{
this.Clear();
}
if (syntax == string.Empty)
{
return;
}
Point p = new Point(10, 10);
foreach (Database.SelectAnalyzer.Consulta.AnalyzerTabelaOrigem _tableQuery in _consulta.Tabelas)
{
bool findTable = false;
foreach (Table _table in _database.Tables)
{
if (Database.SelectAnalyzer.Consulta.AnalyzerTabelaOrigem.CompararNomeTabela(_tableQuery.Banco, _table.Name))
{
Table _newTable = (Table)_table.Clone();
_newTable.Alias = _tableQuery.Apelido;
this.LoadVisualTable(_newTable, p);
p.X += _visualTables[_newTable.FullName].Width + 10;
findTable = true;
break;
}
}
if (!findTable)
{
throw new Error.Excessao(96, _tableQuery.Referencia);
}
}
foreach (Database.SelectAnalyzer.Consulta.AnalyzerCampo _column in _consulta.Campos)
{
if (_column.ColunaCompleta == "*")
{
foreach (VisualTable _vt in _visualTables.Values)
{
_vt.SelectAll();
}
continue;
}
Column _col = _query.FindColumn(_column.NomeBanco);
if (_col == null)
{
_col = new Column(_column.ColunaCompleta);
}
else
{
_col = (Column)_col.Clone();
if (_column.Apelido != string.Empty)
{
_col.Alias = _column.Apelido;
}
else
{
_visualTables[_col.Table.FullName].SetSelectedColumn(_col, true);
}
}
visualFilters.SetShowColumn(_col, true, true);
_query.Columns.Add(_col);
}
foreach (Database.SelectAnalyzer.Consulta.AnalyzerJoin _join in _consulta.Ligacoes)
{
Column _left = null;
Column _right = null;
if (_join.Type == Database.SelectAnalyzer.Consulta.AnalyzerJoinType.RightOuterJoin)
{
throw new Error.Excessao(197);
}
else
{
_left = AssignColumn(_join.Left.ColunaCompleta);
_right = AssignColumn(_join.Right.ColunaCompleta);
}
Join _newJoin = new Join(_left, _right);
if (_join.Type != Database.SelectAnalyzer.Consulta.AnalyzerJoinType.InnerJoin)
{
_newJoin.Operation = Operation.LeftOuterJoin;
}
else
{
_newJoin.Operation = OperationFX.StringToOperation(_join.Operation);
}
AddVisualJoin(_newJoin);
}
this.InputWhere(_consulta.Filtros);
foreach (Database.SelectAnalyzer.Consulta.AnalyzerCampo _column in _consulta.Grupo)
{
Column _col = AssignColumn(_column.ColunaCompleta);
visualFilters.SetGroup(_col, true);
}
foreach (Database.SelectAnalyzer.Consulta.AnalyzerOrder _order in _consulta.Ordem)
{
Order _ord = new Order();
_ord.DescendingOrder = _order.Descendente;
_ord.Column = AssignColumn(_order.Coluna.ColunaCompleta);
_query.Orders.Add(_ord);
}
List <Database.SelectAnalyzer.Consulta.WherePiece> filtros = _consulta.FiltrosGrupo;
if (filtros != null)
{
if (filtros.Count > 0)
{
visualFilters.SetHavingList(filtros);
}
}
this.RenderSQL();
}
/// <summary>
/// Retorna a Consulta SQL Textual
/// </summary>
/// <returns></returns>
internal string BuildSQL()
{
StringBuilder _sql = new StringBuilder("SELECT ");
if (this._columns.Count == 0)
{
_sql.Append("* ");
}
else
{
foreach (Column _column in this._columns)
{
_sql.Append(_column.FullName);
_sql.Append(", ");
}
_sql.Remove(_sql.Length - 2, 1);
}
_sql.Append("\nFROM ");
// Guarda quantas vezes a tebela foi encontrada
int[] _stepsAdded = new int[this._joins.Count];
if (this._tables.Count > 0)
{
StringBuilder _allJoins = new StringBuilder();
bool _inJoin = false;
bool _firstAdd = true;
int _innerJoinCount = 0;
foreach (Table _table in this.Tables)
{
StringBuilder _join = new StringBuilder();
bool _leftOuterJoin = false;
_inJoin = false;
for (int i = 0; i < _joins.Count; i++)
{
if (_joins[i].Left.Table.Alias == _table.Alias || _joins[i].Right.Table.Alias == _table.Alias)
{
_stepsAdded[i]++;
// se já carregou os dois lados do join
if (_stepsAdded[i] == 2)
{
if (_joins[i].Operation == Operation.LeftOuterJoin)
{
_leftOuterJoin = true;
}
if (!_inJoin)
{
_innerJoinCount++;
_join.AppendFormat("JOIN {0} \n\t\tON {1} {2} {3}", _table.FullName, _joins[i].Left.SchemaName, OperationFX.OperationToString(_joins[i].Operation), _joins[i].Right.SchemaName);
_inJoin = true;
}
else
{
_join.AppendFormat("\n\t\t\tAND {0} {1} {2}", _joins[i].Left.SchemaName, OperationFX.OperationToString(_joins[i].Operation), _joins[i].Right.SchemaName);
}
}
}
}
if (_inJoin)
{
if (_innerJoinCount > 1)
{
_allJoins.Append(")");
}
if (_leftOuterJoin)
{
_allJoins.Append("\n\tLEFT OUTER ");
}
else
{
_allJoins.Append("\n\tINNER ");
}
_allJoins.Append(_join.ToString());
}
else
{
if (!_firstAdd)
{
if (_innerJoinCount > 0)
{
_allJoins.Append(")");
}
}
else
{
if (_innerJoinCount > 1)
{
_allJoins.Append(")");
}
}
while (_innerJoinCount > 0)
{
_innerJoinCount--;
_sql.Append("(");
}
if (!_firstAdd)
{
_sql.Append(_allJoins.ToString());
_sql.Append(",\n\t");
_allJoins = new StringBuilder();
}
_allJoins.Append(_table.FullName);
if (_firstAdd)
{
_firstAdd = false;
}
}
}
while (_innerJoinCount > 1)
{
_innerJoinCount--;
_sql.Append("(");
}
_sql.Append(_allJoins.ToString());
}
if (_wheres.Count > 0)
{
_sql.Append("\nWHERE ");
bool _isExpression = false;
string _whereSyntax = string.Empty;
foreach (Where _where in _wheres)
{
_whereSyntax = _where.ToString();
_sql.Append(_whereSyntax);
_isExpression = _where.Operation == Operation.FreeStyle;
if (_isExpression)
{
_whereSyntax = _whereSyntax.ToLower().Trim();
if (!(_whereSyntax.EndsWith("and") || _whereSyntax.EndsWith("or")))
{
_sql.Append(" AND");
}
}
_sql.Insert(_sql.Length - _where.Junction.Length, "\n\t");
_sql.Append(" ");
}
// Tira o último and ou or e o enter \r\n anterior a ele
_sql.Remove(_sql.Length - _wheres[_wheres.Count - 1].Junction.Length - 3, _wheres[_wheres.Count - 1].Junction.Length + 3);
}
if (_groups.Count > 0)
{
_sql.Append("\nGROUP BY ");
foreach (Column _column in this._groups)
{
_sql.Append(_column.SchemaName);
_sql.Append(", ");
}
_sql.Remove(_sql.Length - 2, 1);
}
if (_havings.Count > 0)
{
_sql.Append("\nHAVING ");
foreach (Where _having in _havings)
{
_sql.Append(_having.ToString());
_sql.Insert(_sql.Length - _having.Junction.Length, "\n\t");
_sql.Append(" ");
}
// Tira o último and ou or
_sql.Remove(_sql.Length - _havings[_havings.Count - 1].Junction.Length - 3, _havings[_havings.Count - 1].Junction.Length + 3);
}
if (_orders.Count > 0)
{
_sql.Append("\nORDER BY ");
foreach (Order _order in this._orders)
{
_sql.Append(_order.Column.SchemaName);
if (_order.DescendingOrder)
{
_sql.Append(" DESC");
}
_sql.Append(", ");
}
_sql.Remove(_sql.Length - 2, 1);
}
return(_sql.ToString());
}
public override string ToString()
{
if (this.Operation == Operation.FreeStyle)
{
return(this.Right + " " + this.Junction);
}
StringBuilder sb = new StringBuilder();
for (int ps = 0; ps < paretesisBlockStart; ps++)
{
sb.Append("(");
}
sb.Append("(");
if (this.Left == null)
{
sb.Append("NULL");
}
else
{
sb.Append(this.Left.SchemaName);
}
if (this.LeftSufix != string.Empty)
{
sb.Append(" ");
sb.Append(this.LeftSufix);
}
sb.Append(" ");
sb.Append(OperationFX.OperationToString(this.Operation));
sb.Append(" ");
if (this.Right == null)
{
sb.Append("NULL");
}
else if (this.Right.ToString().ToLower() == "null")
{
sb.Append("NULL");
}
else if (this.Left.Type == "System.String" && !(this.Right.Contains("'") || this.Right.Contains("\"")))
{
sb.Append("'");
sb.Append(this.Right.ToString());
sb.Append("'");
}
else
{
sb.Append(this.Right.ToString());
}
sb.Append(")");
for (int pe = 0; pe < paretesisBlockEnd; pe++)
{
sb.Append(")");
}
sb.Append(" ");
sb.Append(this.Junction);
return(sb.ToString());
}
