示例#1
0
        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));
        }
示例#2
0
        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));
        }
示例#3
0
        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 GetNumEntities(ResultsSet resultsSet, out int numEntities)
        {
            // Initialize
            numEntities = 0;


            List <string> entityNameList = new List <string>();

            foreach (dynamic _dynRow in resultsSet.Data)
            {
                DynamicRowViewModel dynamicRowViewModel = new DynamicRowViewModel();
                foreach (KeyValuePair <string, object> kvp in _dynRow)
                {
                    string[] pp                 = kvp.Key.Split('_');
                    string   entityName         = pp[0];
                    string   existingEntityName = entityNameList.Find(delegate(string t) { return(t == entityName); });
                    if (existingEntityName == null)
                    {
                        entityNameList.Add(entityName);
                    }
                }
            }
            numEntities = entityNameList.Count;

            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 ExportRun(FilterRunViewModel 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.
            *---------------------------------------------------------------------------------------------------------------------------------*/
            FilterRunViewModel filterRunViewModel = null;
            ResultsSet         resultsSet         = null;
            FilterPanelModeler filterPanelModeler = new FilterPanelModeler(Request, this.UserSession, viewModel);

            status = filterPanelModeler.Run(viewModel, out filterRunViewModel, out resultsSet);
            if (!questStatusDef.IsSuccess(status))
            {
                viewModel.questStatus = status;
                return(Json(viewModel, JsonRequestBehavior.AllowGet));
            }

            /*----------------------------------------------------------------------------------------------------------------------------------
            * Return result view model or as Excel
            *---------------------------------------------------------------------------------------------------------------------------------*/
            Response.ClearContent();
            Response.AddHeader("content-disposition", "atachment;filename=" + filterRunViewModel.Name.Replace(" ", "_") + ".xls");
            Response.AddHeader("Content-Type", "application/vnd.ms-excel");
            WriteTsv(resultsSet, Response.Output);
            Response.Flush();
            Response.End();
            return(new EmptyResult());
        }
示例#8
0
 /*==================================================================================================================================
 * Export Routines
 *=================================================================================================================================*/
 public void WriteTsv(ResultsSet resultsSet, TextWriter output)
 {
     foreach (KeyValuePair <string, Column> kvp in resultsSet.ResultColumns)
     {
         output.Write(kvp.Key); // header
         output.Write("\t");
     }
     output.WriteLine();
     foreach (dynamic _dynRow in resultsSet.Data)
     {
         foreach (KeyValuePair <string, object> kvp in _dynRow)
         {
             string value = kvp.Value == null ? "(null)" : kvp.Value.ToString().Replace("\t", " ").Replace("\r", " ").Replace("\n", " ");
             output.Write(value);
             output.Write("\t");
         }
         output.WriteLine();
     }
 }
示例#9
0
        public questStatus PerformBulkUpdateFilterProcedure(BulkUpdateRequest bulkUpdateRequest, FilterProcedure filterProcedure, ResultsSet resultsSet)
        {
            // Initialize
            questStatus     status           = null;
            BulkUpdateLog   bulkUpdateLog    = bLogging ? new BulkUpdateLog() : null;
            BulkUpdateLogId bulkUpdateLogId  = null;
            int             numRows          = 0;
            string          logMessage       = null;
            List <string>   logParameterList = null;


            try
            {
                // Initialize log
                if (bLogging)
                {
                    bulkUpdateLog.Event         = "Initialize";
                    bulkUpdateLog.UserSessionId = this.UserSession.Id;
                    bulkUpdateLog.Username      = this.UserSession.User.Username;
                    bulkUpdateLog.Batch         = Guid.NewGuid().ToString();
                    string Filter = null;
                    status = _dbBulkUpdateLogsMgr.SetFilter(bulkUpdateRequest.Filter, out Filter);
                    if (!questStatusDef.IsSuccess(status))
                    {
                        return(status);
                    }
                    bulkUpdateLog.Filter = Filter;
                }

                // Get database connection string
                TablesetId     tablesetId     = new TablesetId(bulkUpdateRequest.Filter.TablesetId);
                Tableset       tableset       = null;
                DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this.UserSession);
                status = dbTablesetsMgr.Read(tablesetId, out tableset);
                if (!questStatusDef.IsSuccess(status))
                {
                    return(status);
                }
                if (bLogging)
                {
                    string Tableset = null;
                    status = _dbBulkUpdateLogsMgr.SetTableset(tableset, out Tableset);
                    if (!questStatusDef.IsSuccess(status))
                    {
                        return(status);
                    }
                    bulkUpdateLog.Tableset = Tableset;
                }

                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);
                }
                if (bLogging)
                {
                    string Database = null;
                    status = _dbBulkUpdateLogsMgr.SetDatabase(database, out Database);
                    if (!questStatusDef.IsSuccess(status))
                    {
                        return(status);
                    }
                    bulkUpdateLog.Database = Database;
                }


                // Connect and execute
                bool bTransaction = true;  // Update all rows are none of them.
                using (SqlConnection conn = new SqlConnection(database.ConnectionString))
                {
                    bulkUpdateLog.Event = "Connect";
                    conn.Open();
                    SqlTransaction trans = null;
                    if (bTransaction)
                    {
                        trans = conn.BeginTransaction();
                        if (bLogging)
                        {
                            bulkUpdateLog.Event = "BeginTransaction";
                        }
                    }
                    numRows = 0;
                    foreach (dynamic _dynRow in resultsSet.Data)
                    {
                        using (SqlCommand cmd = new SqlCommand(null, conn, trans))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = filterProcedure.Name;

                            // Initialize logging info
                            if (bLogging)
                            {
                                logParameterList         = new List <string>();
                                bulkUpdateLog.Event      = null;
                                bulkUpdateLog.NumRows    = numRows;
                                bulkUpdateLog.Parameters = null;
                                bulkUpdateLog.Message    = null;
                                bulkUpdateLog.Data       = null;
                            }

                            List <string> dataValueList = new List <string>();
                            foreach (FilterProcedureParameter filterParam in filterProcedure.ParameterList)
                            {
                                if (bLogging)
                                {
                                    bulkUpdateLog.Event = "Next Parameter: " + filterParam.ParameterName;
                                }

                                if (filterParam.Direction != "Input")
                                {
                                    SqlParameter sqlParam = new SqlParameter();
                                    if (filterParam.Direction == "ReturnValue")
                                    {
                                        sqlParam.Direction = ParameterDirection.ReturnValue;
                                    }
                                    else if (filterParam.Direction == "Output")
                                    {
                                        sqlParam.Direction = ParameterDirection.Output;
                                    }
                                    else
                                    {
                                        continue; // Input/ouput TODO
                                    }
                                    sqlParam.ParameterName = filterParam.ParameterName;


                                    // TEMPORARY
                                    continue;
                                }
                                if (bLogging)
                                {
                                    logParameterList.Add(String.Format(" Id: {0}, ParameterName: {1}, SqlDbType: {2} ", filterParam.Id, filterParam.ParameterName, filterParam.SqlDbType.ToString()));
                                    string parameterArray = null;
                                    _dbBulkUpdateLogsMgr.SetArray(logParameterList, out parameterArray);
                                    bulkUpdateLog.Parameters = parameterArray;
                                }

                                // If a meta-parameter, fill in its value and continue.
                                bool         bIsMetaParameter = false;
                                SqlParameter sqlMetaParameter = null;
                                if (filterParam.ParameterName.Equals("@_Username", StringComparison.InvariantCultureIgnoreCase))
                                {
                                    bIsMetaParameter       = true;
                                    sqlMetaParameter       = new SqlParameter(filterParam.ParameterName, SqlDbType.NVarChar);
                                    sqlMetaParameter.Value = this.UserSession.User.Username;
                                }
                                else if (filterParam.ParameterName.Equals("@_UserSessionId", StringComparison.InvariantCultureIgnoreCase))
                                {
                                    bIsMetaParameter       = true;
                                    sqlMetaParameter       = new SqlParameter(filterParam.ParameterName, SqlDbType.Int);
                                    sqlMetaParameter.Value = this.UserSession.Id;
                                }
                                else if (filterParam.ParameterName.StartsWith("@_", StringComparison.InvariantCultureIgnoreCase))
                                {
                                    logMessage = String.Format("ERROR: unknown meta-parameter: {0}", filterParam.ParameterName);
                                    if (bLogging)
                                    {
                                        bulkUpdateLog.Message = logMessage;
                                        _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
                                    }
                                    return(new questStatus(Severity.Error, logMessage));
                                }
                                if (bIsMetaParameter)
                                {
                                    cmd.Parameters.Add(sqlMetaParameter);
                                    continue;
                                }


                                // Get the column name from the parameter name
                                FilterItem bulkUpdateFilterItem = bulkUpdateRequest.Filter.FilterItemList.Find(delegate(FilterItem fi)
                                {
                                    return(String.Equals(fi.ParameterName, filterParam.ParameterName, StringComparison.CurrentCultureIgnoreCase));
                                });
                                if (bulkUpdateFilterItem == null)
                                {
                                    if (bTransaction)
                                    {
                                        trans.Rollback();
                                    }
                                    logMessage = String.Format("ERROR: filter item not found for sproc parameter {0}",
                                                               filterParam.ParameterName);
                                    if (bLogging)
                                    {
                                        bulkUpdateLog.Message = logMessage;
                                        _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
                                    }
                                    return(new questStatus(Severity.Error, logMessage));
                                }

                                // Get the bulk update value.
                                // NOTE: THIS COULD BE A TROUBLE SPOT.  ORIGINAL REQUIREMENT WAS SINGLE-ENTITY FILTERS ONLY HAD PROCEDURES.  THUS, THOSE FILTER ITEMS
                                //       WOULD NEVER HAVE NAMES QUALIFIED BY THE ENTITY THEY'RE IN.  BUT, FILTERS WITH ENTITIES THAT HAVE NO COLUMNS IN THE FILTER ITEMS
                                //       TECHNICALLY QUALIFY AS 'SINGLE-ENTITY FILTER'.  THUS, IF THE NAME ALONE DOESN'T MATCH.  GO FOR THE ENTITY_NAME AS A MATCH.
                                BulkUpdateColumnValue bulkUpdateColumnValue = bulkUpdateRequest.Columns.Find(delegate(BulkUpdateColumnValue cv)
                                {
                                    return(cv.Name == bulkUpdateFilterItem.FilterColumn.Name);
                                });
                                if (bulkUpdateColumnValue == null)
                                {
                                    bulkUpdateColumnValue = bulkUpdateRequest.Columns.Find(delegate(BulkUpdateColumnValue cv)
                                    {
                                        string[] parts = cv.Name.Split('_');
                                        if (parts.Length == 2)
                                        {
                                            return(parts[0] == bulkUpdateFilterItem.FilterColumn.ParentEntityType.Name && parts[1] == bulkUpdateFilterItem.FilterColumn.Name);
                                        }
                                        return(false);
                                    });
                                }
                                if (bulkUpdateColumnValue == null)
                                {
                                    return(new questStatus(Severity.Error, String.Format("ERROR: bulk update column value {0} not found in bulk update columns",
                                                                                         bulkUpdateFilterItem.FilterColumn.Name)));
                                }
                                if (bLogging)
                                {
                                    bulkUpdateLog.BulkUpdateColumn = String.Format(" Name: {0}, Value: {1} ", bulkUpdateColumnValue.Name, bulkUpdateColumnValue.Value);
                                }


                                // Determine bulk update value to use.
                                string updateValue = null;
                                if (bulkUpdateColumnValue.bNull)
                                {
                                    updateValue = null;
                                }
                                else if (!string.IsNullOrEmpty(bulkUpdateColumnValue.Value))
                                {
                                    updateValue = bulkUpdateColumnValue.Value;
                                }
                                else if (filterParam.bRequired)
                                {
                                    // Indexing not working, but should be ...
                                    ////updateValue = _dynRow[bulkUpdateColumnValue.Name];
                                    bool bFound = false;
                                    foreach (KeyValuePair <string, object> kvp in _dynRow)
                                    {
                                        if (kvp.Key == bulkUpdateColumnValue.Name)
                                        {
                                            updateValue = kvp.Value != null?kvp.Value.ToString() : null;    // Not sure if we go w/ Null here. But, oh well ...

                                            bFound = true;
                                            break;
                                        }
                                    }
                                    if (!bFound)
                                    {
                                        return(new questStatus(Severity.Error, String.Format("ERROR: filter results column {0} not found to use in bulk update operation",
                                                                                             bulkUpdateColumnValue.Name)));
                                    }
                                }
                                else  // Value is required, use results value since a value not specified in bulk updates.
                                {
                                    updateValue = null;
                                }



                                // Bind the parameter
                                // TODO:REFACTOR
                                SqlDbType    sqlDbType    = (SqlDbType)Enum.Parse(typeof(SqlDbType), filterParam.SqlDbType, true);
                                SqlParameter sqlParameter = new SqlParameter(filterParam.ParameterName, sqlDbType);

                                if (bLogging)
                                {
                                    bulkUpdateLog.Event = "Set Parameter Value";
                                }


                                if (sqlDbType == SqlDbType.Bit)
                                {
                                    bool bValue = updateValue != "0";
                                    sqlParameter.Value = bValue;
                                }
                                else if (sqlDbType == SqlDbType.Int)
                                {
                                    int intValue = Convert.ToInt32(updateValue);
                                    sqlParameter.Value = intValue;
                                }
                                else if (sqlDbType == SqlDbType.NVarChar)
                                {
                                    if (updateValue == null)
                                    {
                                        sqlParameter.Value = DBNull.Value;
                                    }
                                    else
                                    {
                                        sqlParameter.Value = updateValue.ToString();
                                    }
                                }
                                else if (sqlDbType == SqlDbType.VarChar)
                                {
                                    if (updateValue == null)
                                    {
                                        sqlParameter.Value = DBNull.Value;
                                    }
                                    else
                                    {
                                        sqlParameter.Value = updateValue.ToString();
                                    }
                                }
                                else if (sqlDbType == SqlDbType.DateTime)
                                {
                                    if (updateValue == null)
                                    {
                                        sqlParameter.Value = DBNull.Value;
                                    }
                                    else
                                    {
                                        sqlParameter.Value = Convert.ToDateTime(updateValue);
                                    }
                                }
                                else if (sqlDbType == SqlDbType.DateTime2)
                                {
                                    if (updateValue == null)
                                    {
                                        sqlParameter.Value = DBNull.Value;
                                    }
                                    else
                                    {
                                        sqlParameter.Value = Convert.ToDateTime(updateValue);
                                    }
                                }
                                else if (sqlDbType == SqlDbType.Date)
                                {
                                    if (updateValue == null)
                                    {
                                        sqlParameter.Value = DBNull.Value;
                                    }
                                    else
                                    {
                                        sqlParameter.Value = Convert.ToDateTime(updateValue);
                                    }
                                }
                                else if (sqlDbType == SqlDbType.Decimal)
                                {
                                    if (updateValue == null)
                                    {
                                        sqlParameter.Value = DBNull.Value;
                                    }
                                    else
                                    {
                                        sqlParameter.Value = Convert.ToDecimal(updateValue);
                                    }
                                }
                                else
                                {
                                    if (updateValue == null)
                                    {
                                        sqlParameter.Value = DBNull.Value;
                                    }
                                    else
                                    {
                                        sqlParameter.Value = updateValue;
                                    }
                                }
                                cmd.Parameters.Add(sqlParameter);
                                if (bLogging)
                                {
                                    dataValueList.Add(String.Format(" Name: {0}, Value: {1} ", sqlParameter.ParameterName,
                                                                    sqlParameter.Value == DBNull.Value ? "null" : sqlParameter.Value.ToString()));
                                }
                            }
                            // Execute the command
                            try
                            {
                                if (bLogging)
                                {
                                    bulkUpdateLog.Event = "ExecuteNonQuery";
                                }
                                int _numRows = cmd.ExecuteNonQuery();
                                if (_numRows != 1)
                                {
                                    if (bTransaction)
                                    {
                                        trans.Rollback();
                                    }
                                    logMessage = String.Format("ERROR: Bulk update stored procedure failed: Rows: {0}", _numRows);
                                    if (bLogging)
                                    {
                                        bulkUpdateLog.Message = logMessage;
                                        _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
                                    }
                                    return(new questStatus(Severity.Error, logMessage));
                                }
                                else if (bLoggingPerRow)
                                {
                                    bulkUpdateLog.Message          = "Successful bulk update";
                                    bulkUpdateLog.NumRows          = _numRows;
                                    bulkUpdateLog.BulkUpdateColumn = null;

                                    string valueArray = null;
                                    _dbBulkUpdateLogsMgr.SetArray(dataValueList, out valueArray);
                                    bulkUpdateLog.Data = valueArray;

                                    _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
                                }
                            }
                            catch (SqlException ex)
                            {
                                if (bTransaction)
                                {
                                    trans.Rollback();
                                }
                                logMessage = String.Format("SQL EXCEPTION: Bulk update stored procedure {0}: {1}",
                                                           filterProcedure.Name, ex.Message);
                                if (bLogging)
                                {
                                    bulkUpdateLog.Message = logMessage;
                                    _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
                                }
                                return(new questStatus(Severity.Error, logMessage));
                            }
                            catch (System.Exception ex)
                            {
                                if (bTransaction)
                                {
                                    trans.Rollback();
                                }
                                logMessage = String.Format("EXCEPTION: Bulk update stored procedure {0}: {1}",
                                                           filterProcedure.Name, ex.Message);
                                if (bLogging)
                                {
                                    bulkUpdateLog.Message = logMessage;
                                    _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
                                }
                                return(new questStatus(Severity.Error, logMessage));
                            }
                        }
                        numRows += 1;
                    }
                    if (bTransaction)
                    {
                        trans.Commit();
                    }
                    if (bLogging)
                    {
                        bulkUpdateLog.Event            = "COMMIT";
                        bulkUpdateLog.NumRows          = numRows;
                        bulkUpdateLog.BulkUpdateColumn = null;
                        bulkUpdateLog.Message          = "Bulk update operation successful";
                        bulkUpdateLog.Data             = null;

                        _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
                    }
                }
            }
            catch (System.Exception ex)
            {
                logMessage = String.Format("EXCEPTION: Bulk Update Operation: {0}.{1}: {2}",
                                           this.GetType().Name, MethodBase.GetCurrentMethod().Name,
                                           ex.InnerException != null ? ex.InnerException.Message : ex.Message);
                if (bLogging)
                {
                    bulkUpdateLog.Event   = bulkUpdateLog.Event == null ? "EXCEPTION" : bulkUpdateLog.Event;
                    bulkUpdateLog.Message = logMessage;
                    bulkUpdateLog.NumRows = numRows;

                    string parameterArray = null;
                    _dbBulkUpdateLogsMgr.SetArray(logParameterList, out parameterArray);
                    bulkUpdateLog.Parameters = parameterArray;

                    string Exception = null;
                    _dbBulkUpdateLogsMgr.SetException(ex, out Exception);
                    bulkUpdateLog.Data = Exception;

                    _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
                }
                return(new questStatus(Severity.Fatal, logMessage));
            }
            return(new questStatus(Severity.Success));
        }
        //----------------------------------------------------------------------------------------------------------------------------------
        // Validations
        //----------------------------------------------------------------------------------------------------------------------------------
        #endregion


        #region Post-Processing on Filter Runs
        //----------------------------------------------------------------------------------------------------------------------------------
        // Post-Processing on Filter Runs
        //----------------------------------------------------------------------------------------------------------------------------------
        public questStatus TransferResults(ResultsSet resultsSet, out FilterRunViewModel filterRunViewModel)
        {
            return(TransferResults(null, resultsSet, out filterRunViewModel));
        }
示例#11
0
        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));
        }
示例#12
0
        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 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));
        }
        public ActionResult Run(FilterRunViewModel 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.
            *---------------------------------------------------------------------------------------------------------------------------------*/
            if (viewModel.Items.Count == 0)
            {
                string items = Request.Form["Items"].ToString();
                JavaScriptSerializer       javaScriptSerializer = new JavaScriptSerializer();
                List <FilterItemViewModel> itemsList            = javaScriptSerializer.Deserialize <List <FilterItemViewModel> >(items);
                viewModel.Items = itemsList;


                string resultsOptions = Request.Form["_ResultsOptions"].ToString();
                ResultsOptionsViewModel _ResultsOptions = javaScriptSerializer.Deserialize <ResultsOptionsViewModel>(resultsOptions);
                viewModel._ResultsOptions = _ResultsOptions;
            }
            FilterRunViewModel filterRunViewModel = null;
            ResultsSet         resultsSet         = null;
            FilterPanelModeler filterPanelModeler = new FilterPanelModeler(Request, this.UserSession, viewModel);

            status = filterPanelModeler.Run(viewModel, out filterRunViewModel, out resultsSet);
            if (!questStatusDef.IsSuccess(status))
            {
                viewModel.questStatus = status;
                return(Json(viewModel, JsonRequestBehavior.AllowGet));
            }

            /*----------------------------------------------------------------------------------------------------------------------------------
            * Return result view model or as Excel
            *---------------------------------------------------------------------------------------------------------------------------------*/
            if (viewModel.bExportToExcel)
            {
                Response.ClearContent();
                Response.AddHeader("content-disposition", "atachment;filename=" + filterRunViewModel.Name.Replace(" ", "_") + ".xls");
                Response.AddHeader("Content-Type", "application/vnd.ms-excel");
                WriteTsv(resultsSet, Response.Output);
                Response.Flush();
                Response.End();
                return(new EmptyResult());
            }
            else
            {
                status = new questStatus(Severity.Success, "Filter successfully run");
                filterRunViewModel.questStatus = status;
                return(Json(filterRunViewModel, JsonRequestBehavior.AllowGet));
            }
        }