protected virtual string CalculateComputed(ComputedColumn col, Dictionary <string, object> cache, params object[] args)
{
var t = col.Calculator(args);
cache.Add(col.Name, t);
return(t);
}
protected virtual object EvalComputedColumn(ParseTree tree, params object[] paramlist)
{
var result = new ComputedColumn();
result.ColumnName = (string)this.GetValue(tree, TokenType.ColumnName, 0);
result.TableName = (string)this.GetValue(tree, TokenType.TableName, 0);
result.ReturnType = ComputedColumn.StringToReturnType((string)this.GetValue(tree, TokenType.COMPUTED_RETURNTYPE, 0));
result.Expression = (string)this.GetValue(tree, TokenType.PropertyValue, 0);
return(result);
}
public string getComputeColumnsFromClauseQuery()
{
string query = "";
foreach (Column col in _selectedColumns)
{
if (col is ComputedColumn)
{
ComputedColumn compColumn = (ComputedColumn)col;
string compColumnAlias = compColumn.AliasName;
if (compColumn.Name.StartsWith(Functions.FUNC_GROUP_FIRST) || compColumn.Name.StartsWith(Functions.FUNC_GROUP_LAST))
{
List <Parameter> parameters = compColumn.Parameters;
string groupCol = getColumnName(parameters[0].Value);
string orderCol = getColumnName(parameters[1].Value);
string refCol = getColumnName(parameters[2].Value);
string masterTableName = _selectedTables[0].Name;
string masterTableAlias = _selectedTables[0].AliasName;
string alias1 = compColumnAlias + "1";
string alias2 = compColumnAlias + "2";
if (compColumn.Name.StartsWith(Functions.FUNC_GROUP_FIRST))
{
query += " left outer join ( select " + alias1 + "." + groupCol + "," + alias1 + "." + refCol +
" from " + masterTableName + " " + alias1 + " inner join ( " +
"select " + groupCol + ", " + "min(" + orderCol + ") as " + orderCol + " from " + masterTableName + " group by " + groupCol + " ) " +
alias2 + " on " + alias1 + "." + groupCol + " = " + alias2 + "." + groupCol + " and " + alias1 + "." + orderCol + " = " + alias2 + "." + orderCol +
" ) " + compColumnAlias + " on " + masterTableAlias + "." + groupCol + " = " + compColumnAlias + "." + groupCol + " ";
}
else
{
query += " left outer join ( select " + alias1 + "." + groupCol + "," + alias1 + "." + refCol +
" from " + masterTableName + " " + alias1 + " inner join ( " +
"select " + groupCol + ", " + "max(" + orderCol + ") as " + orderCol + " from " + masterTableName + " group by " + groupCol + " ) " +
alias2 + " on " + alias1 + "." + groupCol + " = " + alias2 + "." + groupCol + " and " + alias1 + "." + orderCol + " = " + alias2 + "." + orderCol +
" ) " + compColumnAlias + " on " + masterTableAlias + "." + groupCol + " = " + compColumnAlias + "." + groupCol + " ";
}
}
}
}
return(query);
}
public static string getColumnPartQuery(Column column)
{
if (column is ComputedColumn)
{
ComputedColumn compColumn = (ComputedColumn)column;
if (compColumn.Type == ComputedColumn.FUNCTION)
{
if (compColumn.Name.StartsWith(Functions.FUNC_GROUP_FIRST) || compColumn.Name.StartsWith(Functions.FUNC_GROUP_LAST))
{
List <Parameter> parameters = compColumn.Parameters;
string refCol = getColumnName(parameters[2].Value);
return(compColumn.AliasName + "." + refCol);
}
}
}
return(getColumnPartQuery(column.Name));
}
public void CreateComputedColumn(
Dictionary <string, UserTable> userTables,
IDataRecord reader)
{
var schemaName = Convert.ToString(reader[SchemaNameOrdinal]);
var tableName = Convert.ToString(reader[TableNameOrdinal]);
var userTableNamespaceBuilder = new StringBuilder(schemaName.Length + tableName.Length + 1);
userTableNamespaceBuilder.Append(schemaName).
Append(Constants.Dot).
Append(tableName);
var userTableNamespace = userTableNamespaceBuilder.ToString();
if (!userTables.ContainsKey(userTableNamespace))
{
return;
}
var userTable = userTables[userTableNamespace];
if (userTable == null)
{
return;
}
var computedColumn = new ComputedColumn
{
UserTable = userTable,
ObjectName = Convert.ToString(reader[ObjectNameOrdinal]),
Definition = Convert.ToString(reader[DefinitionOrdinal]),
IsPersisted = Convert.ToBoolean(reader[IsPersistedOrdinal]),
IsNullable = Convert.ToBoolean(reader[IsNullableOrdinal])
};
userTable.ComputedColumns.Add(computedColumn);
}
private void AddCompCol_Click(object sender, RoutedEventArgs e)
{
ComputedColumn computedCol = new ComputedColumn();
if (ComputedColExpTxtBox.Text != "")
{
computedCol.Name = ComputedColExpTxtBox.Text;
}
else
{
string functionName = ComputedColFunctionComboBox.SelectedItem.ToString();
Function function = Functions.getFunction(functionName);
List <Parameter> paramList = new List <Parameter>();
int paramIndex = 0;
foreach (Parameter param in function.Parameters)
{
paramIndex++;
string value = getParamSelectedValue(paramIndex);
Parameter parameter = new Parameter(param.Name, value, param.Type);
paramList.Add(parameter);
}
computedCol.Parameters = paramList;
computedCol.Name = functionName;
computedCol.Type = ComputedColumn.FUNCTION;
if (functionName == "CASE")
{
for (int i = 1; i < Convert.ToInt32(totalCaseCondition); i++)
{
Label lblWhen = (Label)this.FindName("lblWhen" + Convert.ToString(i));
TextBox txtWhen = (TextBox)this.FindName("txtWhen" + Convert.ToString(i));
Label lblThen = (Label)this.FindName("lblThen" + Convert.ToString(i));
TextBox txtThen = (TextBox)this.FindName("txtThen" + Convert.ToString(i));
computedCol.Parameters[0].Value = computedCol.Parameters[0].Value + " " + lblWhen.Content.ToString();
computedCol.Parameters[0].Value = computedCol.Parameters[0].Value + " " + txtWhen.Text.ToString();
computedCol.Parameters[0].Value = computedCol.Parameters[0].Value + " " + lblThen.Content.ToString();
computedCol.Parameters[0].Value = computedCol.Parameters[0].Value + " " + txtThen.Text.ToString();
}
Label lblElse = (Label)this.FindName("lblElse");
TextBox txtElse = (TextBox)this.FindName("txtElse");
computedCol.Parameters[0].Value = computedCol.Parameters[0].Value + " " + lblElse.Content.ToString();
computedCol.Parameters[0].Value = computedCol.Parameters[0].Value + " " + txtElse.Text.ToString();
computedCol.Parameters[0].Value = computedCol.Parameters[0].Value + " " + "END)";
}
if (functionName == "IFNULL")
{
computedCol.Parameters[1].Value = ComputedColifnullTxtBox.Text;
}
else
{
computedCol.Parameters = paramList;
}
}
computedCol.AliasName = ComputedColNameTxtBox.Text;
if (ComputedColFormatComboBox.SelectedValue.ToString() != "Select One")
{
computedCol.Format = ComputedColFormatComboBox.SelectedValue.ToString();
}
if (computedCol != null)
{
if (computedCol.Name != System.String.Empty & computedCol.AliasName != System.String.Empty)
{
IEnumerable <Column> columnsWithSameAlias = _SelectedColCollection.Where(x => x.AliasName == computedCol.AliasName);
if (columnsWithSameAlias.Count() == 0)
{
IEnumerable <Column> columns = _SelectedColCollection.Where(x => x.Name == computedCol.Name & x.AliasName == computedCol.AliasName);
if (columns.Count() == 0)
{
_SelectedColCollection.Add(computedCol);
ComputedColNameTxtBox.Text = "";
ComputedColExpTxtBox.Text = "";
ComputedColFormatComboBox.Text = "Select One";
}
else
{
System.Media.SystemSounds.Beep.Play();
}
}
else
{
System.Media.SystemSounds.Beep.Play();
}
}
}
if (_SelectedColCollection.Count > 11 && isExpanded == false)
{
SelectedColsStackPanel.Width += 16;
isExpanded = true;
}
ComputedColName1.Visibility = System.Windows.Visibility.Hidden;
ComputedColName2.Visibility = System.Windows.Visibility.Hidden;
ComputedColName3.Visibility = System.Windows.Visibility.Hidden;
ComputedColName4.Visibility = System.Windows.Visibility.Hidden;
ComputedColComboBox1.Visibility = System.Windows.Visibility.Hidden;
ComputedColComboBox2.Visibility = System.Windows.Visibility.Hidden;
ComputedColComboBox3.Visibility = System.Windows.Visibility.Hidden;
ComputedColComboBox4.Visibility = System.Windows.Visibility.Hidden;
ComputedColifnullTxtBox.Visibility = System.Windows.Visibility.Hidden;
ComputedColComboBox1.Text = "";
ComputedColComboBox2.Text = "";
ComputedColComboBox3.Text = "";
ComputedColComboBox4.Text = "";
this.ComputedColFunctionComboBox.ItemsSource = Functions.getFunctionNames();
this.ComputedColFunctionComboBox.SelectedIndex = -1;
AddCaseCondition.Visibility = System.Windows.Visibility.Hidden;
RemoveCaseCondition.Visibility = System.Windows.Visibility.Hidden;
}
