private questStatus create(MasterPricingEntities dbContext, List <Quest.Functional.MasterPricing.FilterProcedure> filterProcedureList, out List <Quest.Functional.MasterPricing.FilterProcedure> filterProcedureIdList)
{
// Initialize
filterProcedureIdList = null;
// Perform create
try
{
List <Quest.Services.Dbio.MasterPricing.FilterProcedures> _filterProcedureList = new List <Quest.Services.Dbio.MasterPricing.FilterProcedures>();
foreach (Quest.Functional.MasterPricing.FilterProcedure filterProcedure in filterProcedureList)
{
Quest.Services.Dbio.MasterPricing.FilterProcedures _filterProcedure = new Quest.Services.Dbio.MasterPricing.FilterProcedures();
BufferMgr.TransferBuffer(filterProcedure, _filterProcedure);
_filterProcedureList.Add(_filterProcedure);
}
dbContext.FilterProcedures.AddRange(_filterProcedureList);
dbContext.SaveChanges();
filterProcedureIdList = new List <FilterProcedure>();
foreach (Quest.Services.Dbio.MasterPricing.FilterProcedures _filterProcedure in _filterProcedureList)
{
Quest.Functional.MasterPricing.FilterProcedure filterProcedure = new FilterProcedure();
filterProcedure.Id = _filterProcedure.Id;
filterProcedureIdList.Add(filterProcedure);
}
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().Name, MethodBase.GetCurrentMethod().Name,
ex.InnerException != null ? ex.InnerException.Message : ex.Message)));
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus PerformBulkInsert(BulkInsertRequest bulkInsertRequest)
{
// Initialize
questStatus status = null;
// Read Filter
FilterId filterId = new FilterId(bulkInsertRequest.FilterId);
Filter filter = null;
FiltersMgr filtersMgr = new FiltersMgr(this.UserSession);
status = filtersMgr.Read(filterId, out filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkInsertRequest.Filter = filter;
// Determine if bulk insert filter procedure exists.
FilterProcedure filterProcedure = null;
status = _dbBulkInsertMgr.GetFilterProcedure(bulkInsertRequest, "Insert", out filterProcedure);
if (!questStatusDef.IsSuccessOrWarning(status))
{
return(status);
}
// Perform bulk insert filter procedure if exists.
if (questStatusDef.IsSuccess(status))
{
return(PerformBulkInsertFilterProcedure(bulkInsertRequest, filterProcedure));
}
// Validate bulk insert request
BulkInsertResponse bulkInsertResponse = null;
status = ValidateRequest(bulkInsertRequest, out bulkInsertResponse);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Generate the SQL
status = _dbBulkInsertMgr.GenerateBulkInsertSQL(bulkInsertRequest);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Perform bulk insert.
status = _dbBulkInsertMgr.PerformBulkInsert(bulkInsertRequest);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus PerformBulkUpdate(BulkUpdateRequest bulkUpdateRequest, out int numRows)
{
// Initialize
questStatus status = null;
numRows = -1;
// Get the filter
Filter filter = null;
FilterId filterId = new FilterId(bulkUpdateRequest.FilterId);
FilterMgr filterMgr = new FilterMgr(this.UserSession);
status = filterMgr.GetFilter(filterId, out filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkUpdateRequest.Filter = filter;
// Determine if bulk update filter procedure exists.
FilterProcedure filterProcedure = null;
status = _dbBulkUpdateMgr.GetFilterProcedure(bulkUpdateRequest, "Update", out filterProcedure);
if (!questStatusDef.IsSuccessOrWarning(status))
{
return(status);
}
// Perform bulk update filter procedure if exists.
if (questStatusDef.IsSuccess(status))
{
return(PerformBulkUpdateFilterProcedure(bulkUpdateRequest, filterProcedure));
}
// Generate the SQL
status = _dbBulkUpdateMgr.GenerateBulkUpdateSQL(bulkUpdateRequest);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Perform bulk update.
status = _dbBulkUpdateMgr.PerformBulkUpdate(bulkUpdateRequest, out numRows);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus Update(DbMgrTransaction trans, FilterProcedure filterProcedure)
{
// Initialize
questStatus status = null;
// Update filterProcedure
status = _dbFilterProceduresMgr.Update(trans, filterProcedure);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus Read(FilterProcedureId filterProcedureId, out FilterProcedure filterProcedure)
{
// Initialize
questStatus status = null;
filterProcedure = null;
// Read filterProcedure
status = _dbFilterProceduresMgr.Read(filterProcedureId, out filterProcedure);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
//
// Filter Procedure Utility Routines
//
public questStatus GetFilterProcedure(BulkUpdateRequest bulkUpdateRequest, string Action, out FilterProcedure filterProcedure)
{
// Initialize
questStatus status = null;
filterProcedure = null;
// Get the filter procedures.
FilterId filterId = new FilterId(bulkUpdateRequest.FilterId);
List <FilterProcedure> filterProcedureList = null;
DbFilterProceduresMgr dbFilterProceduresMgr = new DbFilterProceduresMgr(this.UserSession);
status = dbFilterProceduresMgr.Read(filterId, out filterProcedureList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Determine if given action exists.
FilterProcedure UPDATEsproc = filterProcedureList.Find(delegate(FilterProcedure fp) { return(fp.Action == Action); });
if (UPDATEsproc == null)
{
return(new questStatus(Severity.Warning, String.Format("No {0} filter procedure", Action)));
}
// Get parameters
FilterProcedureId filterProcedureId = new FilterProcedureId(UPDATEsproc.Id);
List <FilterProcedureParameter> filterProcedureParameterList = null;
DbFilterProcedureParametersMgr dbFilterProcedureParametersMgr = new DbFilterProcedureParametersMgr(this.UserSession);
status = dbFilterProcedureParametersMgr.Read(filterProcedureId, out filterProcedureParameterList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Return filter procedure with parameters.
UPDATEsproc.ParameterList = filterProcedureParameterList;
filterProcedure = UPDATEsproc;
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Properties
*=================================================================================================================================*/
#endregion
#region Public Methods
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus Create(DbMgrTransaction trans, Quest.Functional.MasterPricing.Database database, FilterProcedure filterProcedure, out FilterProcedureId filterProcedureId)
{
// Initialize
questStatus status = null;
filterProcedureId = null;
// Create filterProcedure
status = _dbFilterProceduresMgr.Create(trans, filterProcedure, out filterProcedureId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Store parameters for procedure
List <FilterProcedureParameter> filterProcedureParameterList = null;
status = GetStoredProdecureParameters(database, filterProcedure.Name, out filterProcedureParameterList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
FilterProcedureParametersMgr filterProcedureParametersMgr = new FilterProcedureParametersMgr(this.UserSession);
foreach (FilterProcedureParameter filterProcedureParameter in filterProcedureParameterList)
{
filterProcedureParameter.FilterProcedureId = filterProcedureId.Id;
FilterProcedureParameterId filterProcedureParameterId = null;
status = filterProcedureParametersMgr.Create(trans, database, filterProcedureParameter, out filterProcedureParameterId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
return(new questStatus(Severity.Success));
}
public questStatus PerformBulkInsertFilterProcedure(BulkInsertRequest bulkInsertRequest, FilterProcedure filterProcedure)
{
// Initialize
questStatus status = null;
// Perform operation.
status = _dbBulkInsertMgr.PerformBulkInsertFilterProcedure(bulkInsertRequest, filterProcedure);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
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));
}
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus Save(FilterPanelViewModel filterPanelViewModel)
{
// Initialize
questStatus status = null;
Quest.Functional.MasterPricing.Filter filter = null;
FilterId filterId = null;
// Validate
if (filterPanelViewModel.Editor.TablesetId < BaseId.VALID_ID)
{
return(new questStatus(Severity.Error, "Invalid Tableset Id"));
}
if (filterPanelViewModel.Editor.FilterId < BaseId.VALID_ID)
{
// For headless agents that want to save all-in-one, not via the Filters panel.
filter = new Functional.MasterPricing.Filter();
BufferMgr.TransferBuffer(filterPanelViewModel.Editor, filter);
filterId = null;
FiltersMgr filtersMgr = new FiltersMgr(this.UserSession);
status = filtersMgr.Create(filter, out filterId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
filterPanelViewModel.Editor.Id = filterId.Id;
filterPanelViewModel.Editor.FilterId = filterId.Id;
}
// Read tableset configuration
TablesetId tablesetId = new TablesetId(filterPanelViewModel.Editor.TablesetId);
TablesetDataManagement tablesetDataManagement = null;
TablesetMgr tablesetMgr = new TablesetMgr(this.UserSession);
status = tablesetMgr.ReadTablesetDataManagement(tablesetId, out tablesetDataManagement);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
#region Transfer filter entities
//
// Transfer filter entities
//
filter = new Functional.MasterPricing.Filter();
filter.TablesetId = filterPanelViewModel.Editor.TablesetId;
filter.Id = filterPanelViewModel.Editor.Id;
foreach (BootstrapTreenodeViewModel filterEntity in filterPanelViewModel.Entities)
{
if (filterEntity.type == "table")
{
FilterTable filterTable = new FilterTable();
filterTable.FilterId = filterPanelViewModel.Editor.Id;
filterTable.Schema = filterEntity.Schema;
filterTable.Name = filterEntity.Name;
filter.FilterTableList.Add(filterTable);
}
else if (filterEntity.type == "view")
{
FilterView filterView = new FilterView();
filterView.FilterId = filterPanelViewModel.Editor.Id;
filterView.Schema = filterEntity.Schema;
filterView.Name = filterEntity.Name;
filter.FilterViewList.Add(filterView);
}
else if (filterEntity.type == "column")
{
FilterColumn filterColumn = new FilterColumn();
filterColumn.FilterId = filterPanelViewModel.Editor.Id;
filterColumn.Name = filterEntity.Name;
filterColumn.TablesetEntityId = filterEntity.ParentId;
if (filterEntity.parentType == "table")
{
TablesetTable parentTablesetTable = tablesetDataManagement.TablesetConfiguration.TablesetTables.Find(delegate(TablesetTable tt) { return(tt.Id == filterEntity.ParentId); });
if (parentTablesetTable == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: column filter entity \"{0}\" (Id {1}) table parent Id {2} not found.",
filterEntity.text, filterEntity.Id, filterEntity.ParentId)));
}
filterColumn.FilterEntityTypeId = FilterEntityType.Table;
// If column table not in filter entities, add it.
BootstrapTreenodeViewModel filterColumnTable = filterPanelViewModel.Entities.Find(delegate(BootstrapTreenodeViewModel n) { return(n.type == "table" && n.Id == filterEntity.ParentId); });
if (filterColumnTable == null)
{
FilterTable filterTable = filter.FilterTableList.Find(delegate(FilterTable t) { return(filterEntity.ParentId == t.TablesetTable.Id); });
if (filterTable == null)
{
filterTable = new FilterTable();
filterTable.FilterId = filterPanelViewModel.Editor.Id;
filterTable.TablesetTable = parentTablesetTable;
filterTable.Schema = parentTablesetTable.Schema;
filterTable.Name = parentTablesetTable.Name;
filter.FilterTableList.Add(filterTable);
}
}
}
else if (filterEntity.parentType == "view")
{
TablesetView parentTablesetView = tablesetDataManagement.TablesetConfiguration.TablesetViews.Find(delegate(TablesetView tv) { return(tv.Id == filterEntity.ParentId); });
if (parentTablesetView == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: column filter entity \"{0}\" (Id {1}) view parent Id {2} not found.",
filterEntity.text, filterEntity.Id, filterEntity.ParentId)));
}
filterColumn.FilterEntityTypeId = FilterEntityType.View;
// If column view not in filter entities, add it.
BootstrapTreenodeViewModel filterColumnView = filterPanelViewModel.Entities.Find(delegate(BootstrapTreenodeViewModel n) { return(n.type == "view" && n.Id == filterEntity.ParentId); });
if (filterColumnView == null)
{
FilterView filterView = filter.FilterViewList.Find(delegate(FilterView v) { return(filterEntity.ParentId == v.TablesetView.Id); });
if (filterView == null)
{
filterView = new FilterView();
filterView.FilterId = filterPanelViewModel.Editor.Id;
filterView.TablesetView = parentTablesetView;
filterView.Schema = parentTablesetView.Schema;
filterView.Name = parentTablesetView.Name;
filter.FilterViewList.Add(filterView);
}
}
}
else
{
return(new questStatus(Severity.Error, String.Format("ERROR: column filter entity \"{0}\" (Id {1}) unknown parent parent type: {2}",
filterEntity.text, filterEntity.Id, filterEntity.ParentId, (filterEntity.parentType == null ? "null" : filterEntity.parentType))));
}
filter.FilterColumnList.Add(filterColumn);
}
else
{
return(new questStatus(Severity.Error, String.Format("Invalid filter entity type: {0}", filterEntity.type)));
}
}
#endregion
#region Transfer filter items
//
// Transfer filter items
//
foreach (FilterItemViewModel filterItemViewModel in filterPanelViewModel.Items)
{
// Filter Item
FilterItem filterItem = new FilterItem();
filterItem.FilterId = filterPanelViewModel.Editor.FilterId;
if (filterItemViewModel.Entity.type == "table")
{
filterItem.FilterEntityTypeId = FilterEntityType.Table;
}
if (filterItemViewModel.Entity.type == "view")
{
filterItem.FilterEntityTypeId = FilterEntityType.View;
}
else if (filterItemViewModel.Entity.type == "column")
{
filterItem.FilterEntityTypeId = FilterEntityType.Column;
}
filterItem.TablesetColumnId = filterItemViewModel.Entity.Id;
filterItem.Label = filterItemViewModel.Label;
filterItem.ParameterName = filterItemViewModel.ParameterName;
filterItem.bHidden = filterItemViewModel.bHidden;
filterItem.bBulkUpdateValueRequired = filterItemViewModel.bBulkUpdateValueRequired;
// Joins
foreach (FilterItemJoinViewModel filterItemJoinViewModel in filterItemViewModel.Joins)
{
FilterItemJoin filterItemJoin = new FilterItemJoin();
filterItemJoin.ColumnId = filterItemJoinViewModel.ColumnId;
filterItemJoin.JoinType = filterItemJoinViewModel.JoinType;
filterItemJoin.Identifier = filterItemJoinViewModel.Identifier;
filterItem.JoinList.Add(filterItemJoin);
}
// Operations
foreach (FilterOperationViewModel filterOperationViewModel in filterItemViewModel.Operations)
{
FilterOperation filterOperation = new FilterOperation();
filterOperation.FilterOperatorId = filterOperationViewModel.Operator;
// Values
foreach (FilterValueViewModel filterValueViewModel in filterOperationViewModel.Values)
{
FilterValue filterValue = new FilterValue();
filterValue.Value = filterValueViewModel.Value;
filterOperation.ValueList.Add(filterValue);
}
filterItem.OperationList.Add(filterOperation);
}
// Lookup
if (filterItemViewModel.Lookup.Id >= BaseId.VALID_ID)
{
filterItem.LookupId = filterItemViewModel.Lookup.Id;
}
// TypeList
if (filterItemViewModel.TypeList.Id >= BaseId.VALID_ID)
{
filterItem.TypeListId = filterItemViewModel.TypeList.Id;
}
filter.FilterItemList.Add(filterItem);
}
#endregion
#region Transfer filter procedures
//
// Transfer filter procedures
//
foreach (FilterProcedureViewModel filterProcedureViewModel in filterPanelViewModel.Procedures)
{
if (filterProcedureViewModel.Id > BaseId.INVALID_ID)
{
FilterProcedure filterProcedure = new FilterProcedure();
BufferMgr.TransferBuffer(filterProcedureViewModel, filterProcedure);
filterProcedure.FilterId = filterPanelViewModel.Editor.FilterId;
filter.FilterProcedureList.Add(filterProcedure);
}
}
#endregion
#region Save filter
//
// Save filter
//
filterId = new FilterId(filterPanelViewModel.Editor.FilterId);
FilterMgr filterMgr = new FilterMgr(this.UserSession);
status = filterMgr.Save(filterId, filter);
if (!questStatusDef.IsSuccess(status))
{
if (questStatusDef.IsWarning(status))
{
return(status);
}
return(new questStatus(status.Severity, String.Format("Error saving filter items: {0}", status.Message)));
}
#endregion
#region Return procedure parameters
//
// Return procedure parameters
//
// Get filter database
Quest.Functional.MasterPricing.Database database = null;
filterMgr.GetFilterDatabase(filterId, out database);
if (filter.FilterProcedureList.Count > 0)
{
foreach (FilterProcedureViewModel filterProcedureViewModel in filterPanelViewModel.Procedures)
{
if ((filterProcedureViewModel.Id == BaseId.INVALID_ID) || (string.IsNullOrEmpty(filterProcedureViewModel.Name)))
{
continue;
}
// Get procedure parameters
List <Quest.Functional.MasterPricing.FilterProcedureParameter> filterProcedureParameterList = null;
status = filterMgr.GetStoredProdecureParameters(database, filterProcedureViewModel.Name, out filterProcedureParameterList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
foreach (FilterProcedureParameter filterProcedureParameter in filterProcedureParameterList)
{
FilterProcedureParameterViewModel filterProcedureParameterViewModel = new FilterProcedureParameterViewModel();
BufferMgr.TransferBuffer(filterProcedureParameter, filterProcedureParameterViewModel, true);
filterProcedureParameterViewModel.Precision = filterProcedureParameter.Precision[0];
filterProcedureParameterViewModel.Scale = filterProcedureParameter.Scale[0];
filterProcedureViewModel.Parameters.Add(filterProcedureParameterViewModel);
}
}
}
#endregion
// Return warning if no items in filter.
if (filter.FilterItemList.Count == 0)
{
return(new questStatus(Severity.Warning, "Filter successfully saved, but has no items"));
}
return(new questStatus(Severity.Success));
}
public questStatus PerformBulkInsertFilterProcedure(BulkInsertRequest bulkInsertRequest, FilterProcedure filterProcedure)
{
// Initialize
questStatus status = null;
BulkInsertLog bulkInsertLog = bLogging ? new BulkInsertLog() : null;
BulkInsertLogId bulkInsertLogId = null;
int numRows = 0;
string logMessage = null;
List <string> logParameterList = null;
try
{
// Initialize log
if (bLogging)
{
bulkInsertLog.Event = "Initialize";
bulkInsertLog.UserSessionId = this.UserSession.Id;
bulkInsertLog.Username = this.UserSession.User.Username;
bulkInsertLog.Batch = Guid.NewGuid().ToString();
string Filter = null;
status = _dbBulkInsertLogsMgr.SetFilter(bulkInsertRequest.Filter, out Filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkInsertLog.Filter = Filter;
}
// Get database connection string
TablesetId tablesetId = new TablesetId(bulkInsertRequest.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 = _dbBulkInsertLogsMgr.SetTableset(tableset, out Tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkInsertLog.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 = _dbBulkInsertLogsMgr.SetDatabase(database, out Database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkInsertLog.Database = Database;
}
// Connect and execute
bool bTransaction = false;
bulkInsertLog.Event = "Open";
using (SqlConnection conn = new SqlConnection(database.ConnectionString))
{
bulkInsertLog.Event = "Connect";
conn.Open();
SqlTransaction trans = null;
if (bTransaction)
{
trans = conn.BeginTransaction();
if (bLogging)
{
bulkInsertLog.Event = "BeginTransaction";
}
}
numRows = 0;
foreach (BulkInsertRow bulkInsertRow in bulkInsertRequest.Rows)
{
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>();
bulkInsertLog.Event = null;
bulkInsertLog.NumRows = numRows;
bulkInsertLog.Parameters = null;
bulkInsertLog.Message = null;
bulkInsertLog.Data = null;
}
// Build each parameter
List <string> dataValueList = new List <string>();
foreach (FilterProcedureParameter filterParam in filterProcedure.ParameterList)
{
if (bLogging)
{
bulkInsertLog.Event = "Next Parameter: " + filterParam.ParameterName;
}
if (filterParam.Direction != "Input")
{
continue;
}
if (bLogging)
{
logParameterList.Add(String.Format(" Id: {0}, ParameterName: {1}, SqlDbType: {2} ", filterParam.Id, filterParam.ParameterName, filterParam.SqlDbType.ToString()));
string parameterArray = null;
_dbBulkInsertLogsMgr.SetArray(logParameterList, out parameterArray);
bulkInsertLog.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)
{
bulkInsertLog.Message = logMessage;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
if (bIsMetaParameter)
{
cmd.Parameters.Add(sqlMetaParameter);
continue;
}
// Not a meta-parameter, so the bulk insert value should be provided.
BulkInsertColumnValue bulkInsertColumnValue = bulkInsertRow.Columns.Find(delegate(BulkInsertColumnValue cv) {
return(cv.Name == filterParam.ParameterName);
});
if (bulkInsertColumnValue == null)
{
if (bTransaction)
{
trans.Rollback();
}
logMessage = String.Format("ERROR: sproc parameter {0} not found in bulk insert columns",
filterParam.ParameterName);
if (bLogging)
{
bulkInsertLog.Message = logMessage;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
if (bLogging)
{
bulkInsertLog.BulkInsertColumn = String.Format(" Name: {0}, Value: {1} ", bulkInsertColumnValue.Name, bulkInsertColumnValue.Value);
}
// Bind the parameter
// TODO:REFACTOR
SqlDbType sqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), filterParam.SqlDbType, true);
SqlParameter sqlParameter = new SqlParameter(filterParam.ParameterName, sqlDbType);
if (bLogging)
{
bulkInsertLog.Event = "Set Parameter Value";
}
if (sqlDbType == SqlDbType.Bit)
{
bool bValue = bulkInsertColumnValue.Value != "0";
sqlParameter.Value = bValue;
}
else if (sqlDbType == SqlDbType.Int)
{
int intValue = Convert.ToInt32(bulkInsertColumnValue.Value);
sqlParameter.Value = intValue;
}
else if (sqlDbType == SqlDbType.NVarChar)
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = bulkInsertColumnValue.Value.ToString();
}
}
else if (sqlDbType == SqlDbType.VarChar)
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = bulkInsertColumnValue.Value.ToString();
}
}
else if (sqlDbType == SqlDbType.DateTime)
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = Convert.ToDateTime(bulkInsertColumnValue.Value);
}
}
else if (sqlDbType == SqlDbType.DateTime2)
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = Convert.ToDateTime(bulkInsertColumnValue.Value);
}
}
else if (sqlDbType == SqlDbType.Date)
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = Convert.ToDateTime(bulkInsertColumnValue.Value);
}
}
else if (sqlDbType == SqlDbType.Decimal)
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = Convert.ToDecimal(bulkInsertColumnValue.Value);
}
}
else
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = bulkInsertColumnValue.Value;
}
}
cmd.Parameters.Add(sqlParameter);
if (bLogging)
{
dataValueList.Add(String.Format(" Name: {0}, Value: {1} ", sqlParameter.ParameterName,
sqlParameter.Value == DBNull.Value ? "null" : sqlParameter.Value.ToString()));
}
}
try
{
if (bLogging)
{
bulkInsertLog.Event = "ExecuteNonQuery";
}
int _numRows = cmd.ExecuteNonQuery();
if (_numRows != 1)
{
if (bTransaction)
{
trans.Rollback();
}
logMessage = String.Format("ERROR: Bulk insert stored procedure failed: Rows: {0}", _numRows);
if (bLogging)
{
bulkInsertLog.Message = logMessage;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
else if (bLoggingPerRow)
{
bulkInsertLog.Message = "Successful bulk insert";
bulkInsertLog.NumRows = _numRows;
bulkInsertLog.BulkInsertColumn = null;
string valueArray = null;
_dbBulkInsertLogsMgr.SetArray(dataValueList, out valueArray);
bulkInsertLog.Data = valueArray;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
}
catch (SqlException ex)
{
if (bTransaction)
{
trans.Rollback();
}
logMessage = String.Format("SQL EXCEPTION: Bulk insert stored procedure {1}: {2}",
numRows, filterProcedure.Name, ex.Message);
if (bLogging)
{
bulkInsertLog.Message = logMessage;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
catch (System.Exception ex)
{
if (bTransaction)
{
trans.Rollback();
}
logMessage = String.Format("After {0} rows, EXCEPTION: Bulk insert stored procedure {0}: {1}",
numRows, filterProcedure.Name, ex.Message);
if (bLogging)
{
bulkInsertLog.Message = logMessage;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
}
numRows += 1;
}
if (bTransaction)
{
trans.Commit();
}
if (bLogging)
{
bulkInsertLog.Event = "COMMIT";
bulkInsertLog.NumRows = numRows;
bulkInsertLog.BulkInsertColumn = null;
bulkInsertLog.Message = "Bulk isnert operation successful";
bulkInsertLog.Data = null;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
}
}
catch (System.Exception ex)
{
logMessage = String.Format("EXCEPTION: Bulk Insert Operation: {0}.{1}: {2}",
this.GetType().Name, MethodBase.GetCurrentMethod().Name,
ex.InnerException != null ? ex.InnerException.Message : ex.Message);
if (bLogging)
{
bulkInsertLog.Event = bulkInsertLog.Event == null ? "EXCEPTION" : bulkInsertLog.Event;
bulkInsertLog.Message = logMessage;
bulkInsertLog.NumRows = numRows;
string parameterArray = null;
_dbBulkInsertLogsMgr.SetArray(logParameterList, out parameterArray);
bulkInsertLog.Parameters = parameterArray;
string Exception = null;
_dbBulkInsertLogsMgr.SetException(ex, out Exception);
bulkInsertLog.Data = Exception;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
return(new questStatus(Severity.Fatal, logMessage));
}
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));
}
