private void refillSelection(QueryInfo qi)
{
resetEverything();
List<string> refillTables = qi.Tables; //get selected tables
List<QColumn> refillColumns = qi.Columns; //get selected columns
List<QColumn> refillAggregatedColums = qi.AggregatedColumns; //get columns that have aggregated function set
List<QColumn> refillGroupByColumns = qi.GroupByColumns; //get columns that have grouped by
foreach (var item in qi.Tables)
{
TreeNode[] treeNodes = tvAvailableColumns.Nodes
.Cast<TreeNode>()
.Where(r => r.Text == item)
.ToArray();
foreach(var node in treeNodes){ //parent nodes
node.Toggle();
foreach (var col in refillColumns)
{
foreach (TreeNode n in node.Nodes) //child nodes
{
int dotIndex = col.Name.IndexOf(".");
int lastSquareIndex = col.Name.IndexOf("]");
int lengthGet = lastSquareIndex - (dotIndex + 2);
if (n.Text == col.Name.Substring(dotIndex + 2, lengthGet))
{
tvAvailableColumns.SelectedNode = n;
}
}
}
}
} //end outer foreach loop
//because when i call treeview.SelectedNode = node, i automatically populate my selectedColumns and selectedTable lists
//so i need a way to set associated aggregate back
//by reset our selectedColumns and selectedTables.
//NOTE: DO NOT RESET AggregateColumns and GroupByColumns list back, it will cause bugs!!
selectedColumns.Clear();
selectedColumns = refillColumns;
selectedTables.Clear();
selectedTables = refillTables;
for (int i = 0; i < refillAggregatedColums.Count; i++)
{
int datagridViewIndex = refillAggregatedColums.ElementAt(i).Index - 1;
dgvSelectedColumns.Rows[datagridViewIndex].Cells["Aggregate"].Value = EnumExtension.ParseEnum<Aggregates>(refillAggregatedColums.ElementAt(i).Aggregate.ToString());
}
for (int i = 0; i < selectedColumns.Count; i++)
{
int datagridViewIndex = selectedColumns.ElementAt(i).Index - 1;
if (selectedColumns.ElementAt(i).Aggregate != null && selectedColumns.ElementAt(i).Aggregate.Name.Equals("LEN"))
{
dgvSelectedColumns.Rows[datagridViewIndex].Cells["Aggregate"].Value = EnumExtension.ParseEnum<Aggregates>(selectedColumns.ElementAt(i).Aggregate.ToString());
}
performTransferHavingToWhereClause(selectedColumns.ElementAt(i)); //a little hack here for changing from having clause to where clause
}
bindGroupColumns();
generateSQLStatement();
}
/// <summary>
/// put all selectedColumns, selectedTables, aggregateColumns, groupByColumns list into 1 object
/// named QueryInfo in order to serialize for writing Json
/// </summary>
/// <returns></returns>
private QueryInfo prepareData()
{
QueryInfo qi = new QueryInfo();
qi.Tables = selectedTables;
qi.Columns = selectedColumns;
qi.AggregatedColumns = aggregatedColumns;
qi.GroupByColumns = groupByColumns;
return qi;
}