public questStatus RemoveTablesetFilters(DbMgrTransaction trans, TablesetId tablesetId)
{
// Initialize
questStatus status = null;
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
status = dbFilterMgr.Delete(trans, tablesetId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus Delete(DbMgrTransaction trans, FilterId filterId)
{
// Initialize
questStatus status = null;
// TODO: DELETE ALL STUFF WITH A FILTER.
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
status = dbFilterMgr.Delete(trans, filterId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus Delete(FilterId filterId)
{
// Initialize
questStatus status = null;
// Delete filter
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
status = dbFilterMgr.Delete(filterId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus Copy(FilterId filterId, out FilterId newFilterId)
{
// Initialize
questStatus status = null;
newFilterId = null;
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
status = dbFilterMgr.Copy(filterId, out newFilterId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus Read(TablesetId tablesetId, out List <Filter> filterList)
{
// Initialize
questStatus status = null;
filterList = null;
// Get all filters for this tableset.
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
status = dbFilterMgr.Read(tablesetId, out filterList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Private Methods
*=================================================================================================================================*/
private questStatus initialize()
{
// Initialize
questStatus status = null;
try
{
_dbFilterMgr = new DbFilterMgr(this.UserSession);
initLogging();
}
catch (System.Exception ex)
{
status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message));
throw new System.Exception(status.Message, ex);
}
return(new questStatus(Severity.Success));
}
public questStatus ValidateRequest(BulkInsertRequest bulkInsertRequest, out BulkInsertResponse bulkInsertResponse)
{
// Initialize
questStatus status = null;
bulkInsertResponse = new BulkInsertResponse();
bulkInsertResponse.BulkInsertRequest = bulkInsertRequest;
// Get the filter.
FilterId filterId = new FilterId(bulkInsertRequest.FilterId);
Filter filter = null;
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
status = dbFilterMgr.GetFilter(filterId, out filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkInsertRequest.Filter = filter;
// Validate the request.
foreach (BulkInsertRow bulkInsertRow in bulkInsertRequest.Rows)
{
BulkInsertRow invalidRow = null;
for (int rdx = 0; rdx < bulkInsertRow.Columns.Count; rdx += 1)
{
BulkInsertColumnValue bulkInsertColumnValue = bulkInsertRow.Columns[rdx];
string entityName = null;
string columnName = null;
if (filter.FilterTableList.Count == 1)
{
entityName = filter.FilterTableList[0].TablesetTable.Table.Name;
columnName = bulkInsertColumnValue.Name;
}
else
{
string[] pp = bulkInsertColumnValue.Name.Split('_');
entityName = pp[0];
columnName = pp[1];
}
FilterColumn filterColumn = null;
FilterTable filterTable = filter.FilterTableList.Find(delegate(FilterTable t) { return(t.TablesetTable.Table.Name == entityName); });
if (filterTable != null)
{
filterColumn = filter.FilterColumnList.Find(delegate(FilterColumn c) {
return(c.TablesetColumn.Column.Name == columnName && c.TablesetColumn.Column.EntityTypeId == EntityType.Table && c.TablesetColumn.Column.EntityId == filterTable.TablesetTable.Table.Id);
});
if (filterColumn == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: filter column not found for bulk insert column {0}, row # {1}",
bulkInsertColumnValue.Name, (rdx + 1))));
}
}
else
{
FilterView filterView = filter.FilterViewList.Find(delegate(FilterView v) { return(v.TablesetView.View.Name == entityName); });
if (filterView == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: filter view not found for bulk insert column {0}, row # {1}",
bulkInsertColumnValue.Name, (rdx + 1))));
}
filterColumn = filter.FilterColumnList.Find(delegate(FilterColumn c) {
return(c.TablesetColumn.Column.Name == columnName && c.TablesetColumn.Column.EntityTypeId == EntityType.View && c.TablesetColumn.Column.EntityId == filterView.TablesetView.View.Id);
});
if (filterColumn == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: filter column not found for bulk insert column {0}, row # {1} (view search)",
bulkInsertColumnValue.Name, (rdx + 1))));
}
}
// Validate the column
status = ValidateBulkInsertColumn(bulkInsertColumnValue, filterColumn);
if (!questStatusDef.IsSuccess(status))
{
if (invalidRow == null)
{
invalidRow = new BulkInsertRow();
}
invalidRow.Columns.Add(bulkInsertColumnValue);
}
}
if (invalidRow != null)
{
bulkInsertResponse.ValidationErrors.Add(invalidRow);
}
}
// TODO: IF ANY INVALID FIELDS, RETURN AN ERROR.
if (bulkInsertResponse.ValidationErrors.Count > 0)
{
return(new questStatus(Severity.Error));
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus Save(FilterId filterId, Filter filter)
{
// Initialize
questStatus status = null;
Mgr mgr = new Mgr(this.UserSession);
DbMgrTransaction trans = null;
FiltersMgr filtersMgr = new FiltersMgr(this.UserSession);
ColumnsMgr columnMgr = new ColumnsMgr(this.UserSession);
try
{
// Validate filter
status = Verify(filterId, filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// BEGIN TRANSACTION
status = mgr.BeginTransaction("SaveFilter" + Guid.NewGuid().ToString(), out trans);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Save the filter
status = Save(trans, filterId, filter);
if (!questStatusDef.IsSuccess(status))
{
mgr.RollbackTransaction(trans);
if (bLogging)
{
Filter _filter = null;
status = filtersMgr.Read(filterId, out _filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
FilterLog filterLog = new FilterLog();
filterLog.Database = "";
filterLog.Tableset = "Tableset.Id=" + filter.TablesetId.ToString();
filterLog.Name = _filter.Name;
filterLog.Event = "SAVE";
filterLog.Data = status.ToString();
FilterLogId filterLogId = null;
_dbFilterLogsMgr.Create(filterLog, out filterLogId);
}
return(status);
}
// COMMIT TRANSACTION
status = mgr.CommitTransaction(trans);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Logging
if (bLogging)
{
Filter _filter = null;
status = filtersMgr.Read(filterId, out _filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
FilterLog filterLog = new FilterLog();
filterLog.Database = "";
filterLog.Tableset = "Tableset.Id=" + filter.TablesetId.ToString();
filterLog.Name = _filter.Name;
filterLog.Event = "SAVE";
filterLog.Data = status.ToString();
FilterLogId filterLogId = null;
_dbFilterLogsMgr.Create(filterLog, out filterLogId);
}
// TODO: REFACTOR TO GET ALL-IN-ONE TRANSACTION
// Generate filter SQL
Filter filterWithSQL = null;
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
status = dbFilterMgr.GenerateFilterSQL(filterId, out filterWithSQL);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Update filter
status = filtersMgr.Update(filterWithSQL);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
catch (System.Exception ex)
{
if (trans != null)
{
mgr.RollbackTransaction(trans);
}
return(new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().Name, MethodBase.GetCurrentMethod().Name,
ex.InnerException != null ? ex.InnerException.Message : ex.Message)));
}
return(new questStatus(Severity.Success));
}
public questStatus Save(DbMgrTransaction trans, FilterId filterId, Filter filter)
{
// Initialize
questStatus status = null;
Mgr mgr = new Mgr(this.UserSession);
ColumnsMgr columnMgr = new ColumnsMgr(this.UserSession);
try
{
// Validate filter
status = Verify(filterId, filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Remove filter entities, items and values. Also, tables and columns.
status = _dbFilterMgr.Clear(trans, filterId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Save filter tables
FilterTablesMgr filterTablesMgr = new FilterTablesMgr(this.UserSession);
foreach (FilterTable filterTable in filter.FilterTableList)
{
filterTable.FilterId = filter.Id;
FilterTableId filterTableId = null;
status = filterTablesMgr.Create(trans, filterTable, out filterTableId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
filterTable.Id = filterTableId.Id;
}
// Save filter views
FilterViewsMgr filterViewsMgr = new FilterViewsMgr(this.UserSession);
foreach (FilterView _filterView in filter.FilterViewList)
{
_filterView.FilterId = filter.Id;
FilterViewId filterViewId = null;
status = filterViewsMgr.Create(trans, _filterView, out filterViewId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
_filterView.Id = filterViewId.Id;
}
// Save filter columns
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
FilterColumnsMgr filterColumnsMgr = new FilterColumnsMgr(this.UserSession);
foreach (FilterColumn filterColumn in filter.FilterColumnList)
{
if (filterColumn.FilterEntityId < BaseId.VALID_ID)
{
// Get the FilterTable or FilterView Id.
// This is a klugie. Gotta rework DataManager panel first, though. No time to do that.
if (filterColumn.FilterEntityTypeId == FilterEntityType.Table)
{
TablesetTable tablesetTable = null;
status = dbFilterMgr.GetTablesetTable(filterColumn, out tablesetTable);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
FilterTable filterTable = filter.FilterTableList.Find(delegate(FilterTable t) { return(t.Schema == tablesetTable.Schema && t.Name == tablesetTable.Name); });
if (filterTable == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: FilterTable not found for TableSetTable Schema:{0} Name:{1}",
tablesetTable.Schema, tablesetTable.Name)));
}
filterColumn.FilterEntityId = filterTable.Id;
}
else if (filterColumn.FilterEntityTypeId == FilterEntityType.View)
{
TablesetView tablesetView = null;
status = dbFilterMgr.GetTablesetView(filterColumn, out tablesetView);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
FilterView filterView = filter.FilterViewList.Find(delegate(FilterView v) { return(v.Schema == tablesetView.Schema && v.Name == tablesetView.Name); });
if (filterView == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: FilterView not found for TableSetTable Schema:{0} Name:{1}",
tablesetView.Schema, tablesetView.Name)));
}
filterColumn.FilterEntityId = filterView.Id;
}
else
{
return(new questStatus(Severity.Error, String.Format("ERROR: FilterColumn FilterEntityTypeId {0} not supported. FilterColumn {1}",
filterColumn.FilterEntityTypeId, filterColumn.Id)));
}
}
// Save
FilterColumnId filterColumnId = null;
status = filterColumnsMgr.Create(trans, filterColumn, out filterColumnId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
filterColumn.Id = filterColumnId.Id;
}
// Save filter items.
FilterItemsMgr filterItemsMgr = new FilterItemsMgr(this.UserSession);
FilterItemJoinsMgr filterItemJoinsMgr = new FilterItemJoinsMgr(this.UserSession);
FilterOperationsMgr filterOperationsMgr = new FilterOperationsMgr(this.UserSession);
FilterValuesMgr filterValuesMgr = new FilterValuesMgr(this.UserSession);
foreach (FilterItem filterItem in filter.FilterItemList)
{
FilterColumn filterColumn = null;
// Get filterItem FilterEntityId
if (filterItem.FilterEntityId < BaseId.VALID_ID)
{
status = dbFilterMgr.GetFilterColumn(filter, filterItem, out filterColumn);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
filterItem.FilterEntityId = filterColumn.Id;
filterItem.FilterColumn = filterColumn; // helps with bookkeeping further down.
}
// Save FilterItem
FilterItemId filterItemId = null;
status = filterItemsMgr.Create(trans, filterItem, out filterItemId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
filterItem.Id = filterItemId.Id;
// Save filter item joins
foreach (FilterItemJoin filterItemJoin in filterItem.JoinList)
{
filterItemJoin.FilterItemId = filterItemId.Id;
if (string.IsNullOrEmpty(filterItemJoin.SourceSchema) || string.IsNullOrEmpty(filterItemJoin.SourceEntityName) || string.IsNullOrEmpty(filterItemJoin.SourceColumnName))
{
// Get join target type (Table or View)
TablesetColumnId tablesetColumnId = new TablesetColumnId(filterItemJoin.ColumnId);
TablesetTable tablesetTable = null;
TablesetView tablesetView = null;
status = getJoinTarget(tablesetColumnId, out tablesetTable, out tablesetView);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
filterItemJoin.TargetEntityTypeId = tablesetTable == null ? FilterEntityType.View : FilterEntityType.Table;
// Get target identifier parts.
string targetSchema = null;
string targetEntityName = null;
string targetColumnName = null;
SQLIdentifier.ParseThreePartIdentifier(filterItemJoin.Identifier, out targetSchema, out targetEntityName, out targetColumnName);
filterItemJoin.TargetSchema = targetSchema;
filterItemJoin.TargetEntityName = targetEntityName;
filterItemJoin.TargetColumnName = targetColumnName;
// Get source identifier parts.
string sourceSchema = null;
string sourceEntityName = null;
string sourceColumnName = null;
if (filterColumn.FilterEntityTypeId == FilterEntityType.Table)
{
FilterTable filterTable = filter.FilterTableList.Find(delegate(FilterTable t) { return(filterColumn.FilterEntityId == t.Id); });
if (filterTable == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: building Join on {0}, FilterTable {1} not found",
filterItemJoin.Identifier, filterColumn.FilterEntityId)));
}
sourceSchema = filterTable.Schema;
sourceEntityName = filterTable.Name;
}
else if (filterColumn.FilterEntityTypeId == FilterEntityType.View)
{
FilterView filterView = filter.FilterViewList.Find(delegate(FilterView v) { return(filterColumn.FilterEntityId == v.Id); });
if (filterView == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: building Join on {0}, FilterView {1} not found",
filterItemJoin.Identifier, filterColumn.FilterEntityId)));
}
sourceSchema = filterView.Schema;
sourceEntityName = filterView.Name;
}
sourceColumnName = filterColumn.Name;
filterItemJoin.SourceSchema = sourceSchema;
filterItemJoin.SourceEntityName = sourceEntityName;
filterItemJoin.SourceColumnName = sourceColumnName;
}
// Create FilterItemJoin
FilterItemJoinId filterItemJoinId = null;
status = filterItemJoinsMgr.Create(trans, filterItemJoin, out filterItemJoinId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
// Save filter operations
foreach (FilterOperation filterOperation in filterItem.OperationList)
{
filterOperation.FilterItemId = filterItem.Id;
FilterOperationId filterOperationId = null;
status = filterOperationsMgr.Create(trans, filterOperation, out filterOperationId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Save filter values
foreach (FilterValue filterValue in filterOperation.ValueList)
{
filterValue.FilterOperationId = filterOperationId.Id;
FilterValueId filterValueId = null;
status = filterValuesMgr.Create(trans, filterValue, out filterValueId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
}
}
// Save filter procedures
// Get the filter's database.
Quest.Functional.MasterPricing.Database database = null;
status = GetFilterDatabase(filterId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// NOTE: business rules reads in procedure parameters and writes them.
FilterProceduresMgr filterProceduresMgr = new FilterProceduresMgr(this.UserSession);
foreach (FilterProcedure filterProcedure in filter.FilterProcedureList)
{
if (string.IsNullOrEmpty(filterProcedure.Name))
{
continue;
}
filterProcedure.FilterId = filter.Id;
FilterProcedureId filterProcedureId = null;
status = filterProceduresMgr.Create(trans, database, filterProcedure, out filterProcedureId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
// NOTE: YOU STILL HAVE TO UPDATE THE FILTER'S SQL. BUT, CAN'T HERE, B/C YOU'RE IN A TRANSACTION.
// TODO: REFACTOR SO YOU CAN, BUT GOTTA GET A PARAMETERS MAKE-REQUIRED UPDATE OUT THERE FIRST.
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().Name, MethodBase.GetCurrentMethod().Name,
ex.InnerException != null ? ex.InnerException.Message : ex.Message)));
}
return(new questStatus(Severity.Success));
}
/*----------------------------------------------------------------------------------------------------------------------------------
* SQL-based
*---------------------------------------------------------------------------------------------------------------------------------*/
public questStatus GenerateBulkInsertSQL(BulkInsertRequest bulkInsertRequest)
{
// Initialize
questStatus status = null;
Filter filter = null;
// If filter not included, get it.
if (bulkInsertRequest.Filter == null)
{
FilterId filterId = new FilterId(bulkInsertRequest.FilterId);
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
status = dbFilterMgr.GetFilter(filterId, out filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkInsertRequest.Filter = filter;
}
filter = bulkInsertRequest.Filter;
// TEMPORARY: IF MORE THAN ONE TABLE, NOT SUPPORTED
int numEntities = 0;
status = getNumEntities(bulkInsertRequest, out numEntities);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
if (numEntities != 1)
{
return(new questStatus(Severity.Error, "Single-table inserts only are supported via SQL generation. Use INSERT sproc."));
}
StringBuilder sbINSERTRows = new StringBuilder();
for (int rdx = 0; rdx < bulkInsertRequest.Rows.Count; rdx += 1)
{
BulkInsertRow bulkInsertRow = bulkInsertRequest.Rows[rdx];
StringBuilder sbINSERTSql = new StringBuilder("INSERT INTO ");
sbINSERTSql.AppendLine(String.Format(" [{0}].[{1}] ", filter.FilterTableList[0].TablesetTable.Table.Schema, filter.FilterTableList[0].TablesetTable.Table.Name));
// Build column list
sbINSERTSql.Append(" ( ");
for (int cidx = 0; cidx < bulkInsertRow.Columns.Count; cidx += 1)
{
BulkInsertColumnValue bulkInsertColumnValue = bulkInsertRow.Columns[cidx];
status = getFilterColumn(bulkInsertColumnValue, filter);
if (!questStatusDef.IsSuccess(status))
{
return(new questStatus(String.Format("ERROR: getting FilterColumn on row {0}: {1}", (rdx + 1), status.Message)));
}
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.bIsIdentity)
{
continue;
}
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.bIsAutoIncrement)
{
continue;
}
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.bIsReadOnly)
{
continue;
}
// Append column to INSERT clause
if (filter.FilterTableList.Count == 1)
{
sbINSERTSql.AppendLine(String.Format(" [{0}]", bulkInsertColumnValue.Name));
}
else
{
string[] pp = bulkInsertColumnValue.Name.Split('_');
sbINSERTSql.AppendLine(String.Format(" [{0}]", pp[1]));
}
// Comma between columns
if (cidx + 1 < bulkInsertRow.Columns.Count)
{
sbINSERTSql.Append(", ");
}
else
{
sbINSERTSql.Append(" ");
}
}
sbINSERTSql.Append(" ) ");
// Build value list
sbINSERTSql.Append(" VALUES ( ");
for (int cidx = 0; cidx < bulkInsertRow.Columns.Count; cidx += 1)
{
BulkInsertColumnValue bulkInsertColumnValue = bulkInsertRow.Columns[cidx];
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.bIsIdentity)
{
continue;
}
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.bIsAutoIncrement)
{
continue;
}
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.bIsReadOnly)
{
continue;
}
// Append to VALUES clause
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.DataTypeName.Contains("varchar"))
{
if (bulkInsertColumnValue.Value == null)
{
sbINSERTSql.Append(" NULL ");
}
else
{
sbINSERTSql.Append(" '" + bulkInsertColumnValue.Value + "' ");
}
}
else if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.DataTypeName.Contains("date"))
{
if (bulkInsertColumnValue.Value == null)
{
sbINSERTSql.Append(" NULL ");
}
else
{
sbINSERTSql.Append(" '" + bulkInsertColumnValue.Value + "' ");
}
}
else
{
if (bulkInsertColumnValue.Value == null)
{
sbINSERTSql.Append(" NULL ");
}
else
{
sbINSERTSql.Append(bulkInsertColumnValue.Value);
}
}
// Comma between values
if (cidx + 1 < bulkInsertRow.Columns.Count)
{
sbINSERTSql.Append(", ");
}
else
{
sbINSERTSql.Append(" ");
}
}
sbINSERTSql.Append(" ) ");
// Append to job SQL.
sbINSERTRows.AppendLine(sbINSERTSql.ToString());
sbINSERTRows.AppendLine();
}
bulkInsertRequest.SQL = sbINSERTRows.ToString();
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus ExecuteFilter(RunFilterRequest runFilterRequest, out ResultsSet resultsSet)
{
// Initialize
questStatus status = null;
resultsSet = null;
// Get the filter
FilterId filterId = new FilterId(runFilterRequest.FilterId.Id);
Filter filter = null;
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
status = dbFilterMgr.GetFilter(filterId, out filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// If no SQL, return.
if (string.IsNullOrEmpty(filter.SQL))
{
return(new questStatus(Severity.Error, "Filter has no SQL"));
}
// Read the tableset
TablesetId tablesetId = new TablesetId(filter.TablesetId);
Tableset tableset = null;
DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this.UserSession);
status = dbTablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Read the database
DatabaseId databaseId = new DatabaseId(tableset.DatabaseId);
Quest.Functional.MasterPricing.Database database = null;
DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this.UserSession);
status = dbDatabasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Execute SQL.
try {
status = executeSQL(runFilterRequest, database, filter, out resultsSet);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Error, String.Format("EXCEPTION: executing filter SQL: {0}", ex.Message)));
}
// klugie: Get number of FROM entities
string FROMClause = null;
List <FilterEntity> FROMEntityList = null;
List <JoinEntity> joinEntityList = null;
DbFilterSQLMgr dbFilterSQLMgr = new DbFilterSQLMgr();
status = dbFilterSQLMgr.GetFROMEntities(filter, out FROMClause, out FROMEntityList, out joinEntityList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
int numItemEntities = FROMEntityList.Count + joinEntityList.Count;
// Append lookup or type list Id's to result columns with lookups.
// NOTE: Lookups and typeList are mutually exclusive.
FilterItem filterItem = null;
try {
for (int idx = 0; idx < filter.FilterItemList.Count; idx += 1)
{
filterItem = filter.FilterItemList[idx];
string columnIdentifier = null;
FilterColumn filterColumn = null;
status = GetResultsColumnIdentifier(filter, filterItem, numItemEntities, out columnIdentifier, out filterColumn);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
if (columnIdentifier == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: columnIdentifier is NULL for filterItem {0} FilterId: {1}",
filterItem.Id, filterItem.FilterId)));
}
if (filterColumn == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: filterColumn is NULL for filterItem {0} FilterId: {1}",
filterItem.Id, filterItem.FilterId)));
}
if (!string.IsNullOrEmpty(filterItem.Label))
{
resultsSet.ResultColumns[columnIdentifier].Name = filterColumn.TablesetColumn.Name;
resultsSet.ResultColumns[columnIdentifier].Label = filterItem.Label;
}
if (filterItem.LookupId.HasValue)
{
resultsSet.ResultColumns[columnIdentifier].LookupId = filterItem.LookupId;
}
if (filterItem.TypeListId.HasValue)
{
resultsSet.ResultColumns[columnIdentifier].TypeListId = filterItem.TypeListId;
}
resultsSet.ResultColumns[columnIdentifier].bIsHidden = filterItem.bHidden;
}
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Error, String.Format("EXCEPTION: building filter results set with FilterItem {0}: {1}",
filterItem.Id, ex.Message)));
}
return(new questStatus(Severity.Success));
}
