public questStatus Run(RunFilterRequest runFilterRequest, Filter filter, out ResultsSet resultsSet)
{
// Initialize
questStatus status = null;
resultsSet = null;
// Run the filter
DbResultsMgr dbResultsMgr = new DbResultsMgr(this.UserSession);
status = dbResultsMgr.Run(runFilterRequest, filter, out resultsSet);
if (bLogging)
{
FilterLog filterLog = new FilterLog();
filterLog.Database = "";
filterLog.Tableset = "";
filterLog.Name = "FilterId=" + runFilterRequest.FilterId.ToString();
filterLog.Event = "RUN";
filterLog.Data = String.Format("RowLimit: {0} ColLimit: {1} PageNumber: {2} PageSize: {3} Result rows: {4} questStatus: {5}",
runFilterRequest.RowLimit, runFilterRequest.ColLimit, runFilterRequest.PageNumber, runFilterRequest.PageSize, resultsSet.NumRows, status.ToString());
FilterLogId filterLogId = null;
_dbFilterLogsMgr.Create(filterLog, out filterLogId);
}
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus ExecuteFilter(RunFilterRequest runFilterRequest, out ResultsSet resultsSet)
{
// TODO: business rules about what can/cannot be saved.
// Initialize
questStatus status = null;
resultsSet = null;
DbResultsMgr dbResultsMgr = new DbResultsMgr(this.UserSession);
// Execute the filter
status = dbResultsMgr.ExecuteFilter(runFilterRequest, out resultsSet);
if (bLogging)
{
FilterLog filterLog = new FilterLog();
filterLog.Database = "";
filterLog.Tableset = "";
filterLog.Name = "FilterId=" + runFilterRequest.FilterId.ToString();
filterLog.Event = "EXECUTE";
filterLog.Data = String.Format("RowLimit: {0} ColLimit: {1} PageNumber: {2} PageSize: {3} Result rows: {4} questStatus: {5}",
runFilterRequest.RowLimit, runFilterRequest.ColLimit, runFilterRequest.PageNumber, runFilterRequest.PageSize,
resultsSet == null ? "(N/A)" : resultsSet.NumRows.ToString(),
status.ToString());
FilterLogId filterLogId = null;
_dbFilterLogsMgr.Create(filterLog, out filterLogId);
}
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus Run(RunFilterRequest runFilterRequest, out FilterRunViewModel filterRunViewModel)
{
// Initialize
questStatus status = null;
filterRunViewModel = null;
// Execute filter
ResultsSet resultsSet = null;
FilterMgr filterMgr = new FilterMgr(this.UserSession);
status = filterMgr.ExecuteFilter(runFilterRequest, out resultsSet);
if (!questStatusDef.IsSuccess(status))
{
return(new questStatus(status.Severity, String.Format("Error executing filter Id={0}: {1}",
runFilterRequest.FilterId.Id, status.Message)));
}
// Transfer results to view model.
status = TransferResults(runFilterRequest, resultsSet, out filterRunViewModel);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus PerformBulkUpdateFilterProcedure(BulkUpdateRequest bulkUpdateRequest, FilterProcedure filterProcedure)
{
// Initialize
questStatus status = null;
// Execute filter
RunFilterRequest runFilterRequest = new RunFilterRequest();
runFilterRequest.FilterId.Id = bulkUpdateRequest.FilterId;
ResultsSet resultsSet = null;
FilterMgr filterMgr = new FilterMgr(this.UserSession);
status = filterMgr.ExecuteFilter(runFilterRequest, out resultsSet);
if (!questStatusDef.IsSuccess(status))
{
return(new questStatus(status.Severity, String.Format("Error executing filter Id={0}: {1}",
runFilterRequest.FilterId.Id, status.Message)));
}
// Perform operation.
status = _dbBulkUpdateMgr.PerformBulkUpdateFilterProcedure(bulkUpdateRequest, filterProcedure, resultsSet);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus TransferResults(RunFilterRequest runFilterRequest, ResultsSet resultsSet, out FilterRunViewModel filterRunViewModel)
{
// Initialize
questStatus status = null;
filterRunViewModel = null;
// Get number of entities.
int numEntities = 0;
status = GetNumEntities(resultsSet, out numEntities);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Transfer model
filterRunViewModel = new FilterRunViewModel();
filterRunViewModel.NumRows = resultsSet.NumRows;
filterRunViewModel.QueryResponse.TotalRecords = resultsSet.NumRows;
filterRunViewModel.Id = runFilterRequest != null ? runFilterRequest.FilterId.Id : BaseId.INVALID_ID;
filterRunViewModel.FilterId = runFilterRequest != null ? runFilterRequest.FilterId.Id : BaseId.INVALID_ID;
foreach (KeyValuePair <string, Column> kvp in resultsSet.ResultColumns)
{
ColumnHeaderViewModel columnHeaderViewModel = new ColumnHeaderViewModel();
BufferMgr.TransferBuffer(kvp.Value, columnHeaderViewModel, true);
columnHeaderViewModel.Name = kvp.Value.Name;
columnHeaderViewModel.Label = MakeColumnLabel(kvp, numEntities);
columnHeaderViewModel.Type = kvp.Value.DataTypeName;
filterRunViewModel.Results.Columns.Add(columnHeaderViewModel);
}
foreach (dynamic _dynRow in resultsSet.Data)
{
DynamicRowViewModel dynamicRowViewModel = new DynamicRowViewModel();
int cidx = 0;
foreach (KeyValuePair <string, object> kvp in _dynRow)
{
ColumnValueViewModel columnValueViewModel = new ColumnValueViewModel();
columnValueViewModel.Name = string.IsNullOrEmpty(filterRunViewModel.Results.Columns[cidx].Label) ?
filterRunViewModel.Results.Columns[cidx].Name : filterRunViewModel.Results.Columns[cidx].Label;
columnValueViewModel.Label = filterRunViewModel.Results.Columns[cidx].Label;
columnValueViewModel.Value = kvp.Value == null ? "(null)" : kvp.Value.ToString();
dynamicRowViewModel.ColumnValues.Add(columnValueViewModel);
cidx += 1;
}
filterRunViewModel.Results.Items.Add(dynamicRowViewModel);
}
return(new questStatus(Severity.Success));
}
public ActionResult Run(FilterEditorViewModel viewModel)
{
questStatus status = null;
/*----------------------------------------------------------------------------------------------------------------------------------
* Log Operation
*---------------------------------------------------------------------------------------------------------------------------------*/
status = LogOperation();
if (!questStatusDef.IsSuccess(status))
{
viewModel.questStatus = status;
return(Json(viewModel, JsonRequestBehavior.AllowGet));
}
/*----------------------------------------------------------------------------------------------------------------------------------
* Authorize
*---------------------------------------------------------------------------------------------------------------------------------*/
status = Authorize(viewModel._ctx);
if (!questStatusDef.IsSuccess(status))
{
viewModel.questStatus = status;
return(Json(viewModel, JsonRequestBehavior.AllowGet));
}
/*----------------------------------------------------------------------------------------------------------------------------------
* Perform operation.
*---------------------------------------------------------------------------------------------------------------------------------*/
RunFilterRequest runFilterRequest = new RunFilterRequest();
runFilterRequest.FilterId.Id = viewModel.FilterId;
runFilterRequest.RowLimit = viewModel._ResultsOptions.RowLimit;
runFilterRequest.ColLimit = viewModel._ResultsOptions.ColLimit;
FilterRunViewModel filterRunViewModel = null;
FilterPanelModeler filterPanelModeler = new FilterPanelModeler(Request, this.UserSession, viewModel);
status = filterPanelModeler.Run(runFilterRequest, out filterRunViewModel);
if (!questStatusDef.IsSuccess(status))
{
viewModel.questStatus = status;
return(Json(viewModel, JsonRequestBehavior.AllowGet));
}
/*----------------------------------------------------------------------------------------------------------------------------------
* Return result.
*---------------------------------------------------------------------------------------------------------------------------------*/
status = new questStatus(Severity.Success, "Filter successfully run");
filterRunViewModel.questStatus = status;
return(Json(filterRunViewModel, JsonRequestBehavior.AllowGet));
}
public questStatus Export(FilterResultsExportViewModel filterResultsExportViewModel, out ResultsSet resultsSet)
{
// Initialize
questStatus status = null;
resultsSet = null;
if (filterResultsExportViewModel._ResultsOptions == null)
{
filterResultsExportViewModel._ResultsOptions = new ResultsOptionsViewModel();
filterResultsExportViewModel._ResultsOptions.RowLimit = "";
filterResultsExportViewModel._ResultsOptions.ColLimit = "";
}
// Fill out a run rqeuest
RunFilterRequest runFilterRequest = new RunFilterRequest();
runFilterRequest.FilterId.Id = filterResultsExportViewModel.Id;
if ((filterResultsExportViewModel.RowLimit != null) && (filterResultsExportViewModel.RowLimit.Trim().Length > 0))
{
runFilterRequest.RowLimit = filterResultsExportViewModel.RowLimit.Trim();
}
else if ((filterResultsExportViewModel._ResultsOptions.RowLimit != null) && (filterResultsExportViewModel._ResultsOptions.RowLimit.Trim().Length > 0))
{
runFilterRequest.RowLimit = filterResultsExportViewModel._ResultsOptions.RowLimit.Trim();
}
if ((filterResultsExportViewModel.ColLimit != null) && (filterResultsExportViewModel.ColLimit.Trim().Length > 0))
{
runFilterRequest.ColLimit = filterResultsExportViewModel.ColLimit.Trim();
}
else if ((filterResultsExportViewModel._ResultsOptions.ColLimit != null) && (filterResultsExportViewModel._ResultsOptions.ColLimit.Trim().Length > 0))
{
runFilterRequest.ColLimit = filterResultsExportViewModel._ResultsOptions.ColLimit.Trim();
}
// Execute filter
resultsSet = null;
FilterMgr filterMgr = new FilterMgr(this.UserSession);
status = filterMgr.ExecuteFilter(runFilterRequest, out resultsSet);
if (!questStatusDef.IsSuccess(status))
{
return(new questStatus(status.Severity, String.Format("Error executing filter Id={0}: {1}",
runFilterRequest.FilterId.Id, status.Message)));
}
return(new questStatus(Severity.Success));
}
public questStatus Run(FilterRunViewModel filterRunViewModel, out FilterRunViewModel filterRunResultsViewModel, out ResultsSet resultsSet)
{
// Initialize
questStatus status = null;
filterRunResultsViewModel = null;
resultsSet = null;
// NOTE: THIS IS FOR RUNNING A FILTER THAT IS --NOT-- IN THE DATABASE. HOWEVER IT IS *BASED* ON A FILTER IN THE DATABASE, THUS A VALID FILTER ID MUST BE GIVEN.
FilterMgr filterMgr = new FilterMgr(this.UserSession);
// Get the filter
FilterId filterId = new FilterId(filterRunViewModel.FilterId);
Quest.Functional.MasterPricing.Filter filterFROMDatabase = null;
status = filterMgr.GetFilter(filterId, out filterFROMDatabase);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Merge view model into filter from database.
Quest.Functional.MasterPricing.Filter filter = null;
status = MergeFilterEditorViewModel(filterRunViewModel, filterFROMDatabase, out filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Verify the filter
status = filterMgr.Verify(filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Generate filter SQL
Quest.Functional.MasterPricing.Filter filterWithSQL = null;
status = filterMgr.GenerateFilterSQL(filter, out filterWithSQL);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Run the filter
RunFilterRequest runFilterRequest = new RunFilterRequest(); // Filter Id doesn't matter, non-DB filter run request.
runFilterRequest.RowLimit = filterRunViewModel._ResultsOptions.RowLimit;
runFilterRequest.ColLimit = filterRunViewModel._ResultsOptions.ColLimit;
status = filterMgr.Run(runFilterRequest, filterWithSQL, out resultsSet);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Transfer results to view model.
status = TransferResults(resultsSet, out filterRunResultsViewModel);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
if (string.IsNullOrEmpty(filterRunViewModel.Name))
{
filterRunResultsViewModel.Name = filterFROMDatabase.Name;
}
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));
}
private questStatus executeSQLWithPaging(RunFilterRequest runFilterRequest, Quest.Functional.MasterPricing.Database database, Filter filter, out ResultsSet resultSet)
{
// Initialize
questStatus status = null;
resultSet = null;
FilterId filterId = new FilterId(filter.Id);
// Execute SQL
try
{
using (SqlConnection sqlConnection = new SqlConnection(database.ConnectionString))
{
sqlConnection.Open();
string sql = filter.SQL;
// Wrap SQL in paging context
if (runFilterRequest != null)
{
if (!string.IsNullOrEmpty(runFilterRequest.RowLimit))
{
var _sql = sql.Replace("SELECT DISTINCT", "SELECT DISTINCT TOP " + runFilterRequest.RowLimit);
sql = _sql;
}
////// WORKING SQL EXAMPLE
////SELECT *
////FROM (SELECT ROW_NUMBER() OVER(ORDER BY[T1].[UOM] ASC) AS RowNum, [T1].[Id] AS 'Id', [T1].[Type] AS 'Type', [T1].[UOM] AS 'UOM', [T1].[Size] AS 'Size', [T1].[StartDate] AS 'StartDate', [T1].[EndDate] AS 'EndDate', [T1].[CreateDate] AS 'CreateDate', [T1].[CreateUser] AS 'CreateUser', [T1].[UpdateDate] AS 'UpdateDate', [T1].[UpdateUser] AS 'UpdateUser', [T1].[Material] AS 'Material', [T1].[DisplayName] AS 'DisplayName', [T1].[MaterialUOM] AS 'MaterialUOM', [T1].[NetSuiteEquipId] AS 'NetSuiteEquipId'
//// FROM[dbo].[QuestEquipment] T1
//// ----WHERE OrderDate >= '1980-01-01'
//// ) AS RowConstrainedResult
////WHERE RowNum >= 1
//// AND RowNum <= 20
////ORDER BY RowNum
}
using (SqlCommand cmd = sqlConnection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
resultSet = new ResultsSet(filterId);
Dictionary <string, Column> dynamicType = null;
status = BuildType(rdr, out dynamicType);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
resultSet.ResultColumns = dynamicType;
while (rdr.Read())
{
dynamic resultRow = null;
status = GetRow(rdr, out resultRow);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
resultSet.Data.Add(resultRow);
}
}
}
}
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Fatal, String.Format("EXCEPTION: executing filter {0} SQL: {1}",
filter.Name, ex.Message)));
}
return(new questStatus(Severity.Success));
}
//
// SQL Execution
//
private questStatus executeSQL(RunFilterRequest runFilterRequest, Quest.Functional.MasterPricing.Database database, Filter filter, out ResultsSet resultSet)
{
// Initialize
questStatus status = null;
resultSet = null;
FilterId filterId = new FilterId(filter.Id);
int numRows = -1;
// Execute SQL
try
{
using (SqlConnection sqlConnection = new SqlConnection(database.ConnectionString))
{
sqlConnection.Open();
string sql = filter.SQL;
// klugie: get row count
using (SqlCommand cmd = sqlConnection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(rdr);
numRows = dt.Rows.Count;
}
}
// Apply run requests
// TODO: TEMPORARY
if (runFilterRequest != null)
{
if (runFilterRequest.RowLimit != null && runFilterRequest.RowLimit.Trim().Length > 0)
{
var _sql = sql.Replace("SELECT DISTINCT", "SELECT DISTINCT TOP " + runFilterRequest.RowLimit);
sql = _sql;
}
}
using (SqlCommand cmd = sqlConnection.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
resultSet = new ResultsSet(filterId);
resultSet.NumRows = numRows;
Dictionary <string, Column> dynamicType = null;
status = BuildType(rdr, out dynamicType);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
resultSet.ResultColumns = dynamicType;
while (rdr.Read())
{
dynamic resultRow = null;
status = GetRow(rdr, out resultRow);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
resultSet.Data.Add(resultRow);
}
}
}
}
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Fatal, String.Format("EXCEPTION: executing filter {0} SQL: {1}",
filter.Name, ex.Message)));
}
return(new questStatus(Severity.Success));
}
