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)); }