/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus GenerateBulkUpdateSQL(BulkUpdateRequest bulkUpdateRequest)
{
// Initialize
questStatus status = null;
StringBuilder sbBulkUPDATE = new StringBuilder();
// Validate
if (bulkUpdateRequest.Filter.FilterTableList.Count != 1)
{
return(new questStatus(Severity.Error, String.Format("ERROR: currently, only single-table filters supported with BUlk Update")));
}
// Get info on FROM clause on filter SQL
string FROMClause = null;
List <FilterEntity> FROMEntityList = null;
List <JoinEntity> joinEntityList = null;
DbFilterSQLMgr dbFilterSQLMgr = new DbFilterSQLMgr();
status = dbFilterSQLMgr.GetFROMEntities(bulkUpdateRequest.Filter, out FROMClause, out FROMEntityList, out joinEntityList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
sbBulkUPDATE.AppendLine(String.Format("UPDATE [{0}].[{1}] SET ",
bulkUpdateRequest.Filter.FilterTableList[0].TablesetTable.Table.Schema, bulkUpdateRequest.Filter.FilterTableList[0].TablesetTable.Table.Name));
List <string> setClauseList = new List <string>();
for (int idx = 0; idx < bulkUpdateRequest.Columns.Count; idx += 1)
{
BulkUpdateColumnValue bulkUpdateColumnValue = bulkUpdateRequest.Columns[idx];
string setClause = null;
status = getFilterColumn(bulkUpdateColumnValue, bulkUpdateRequest.Filter, FROMEntityList.Count);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
if (bulkUpdateColumnValue.bNull)
{
setClause += String.Format(" [{0}] = NULL ", bulkUpdateColumnValue.Name);
setClauseList.Add(setClause);
}
else if (!string.IsNullOrEmpty(bulkUpdateColumnValue.Value))
{
int _value = 0;
if (int.TryParse(bulkUpdateColumnValue.Value, out _value))
{
if (_value == -1 && bulkUpdateColumnValue.LookupId >= BaseId.VALID_ID)
{
continue;
}
}
setClause += String.Format(" [{0}] = {1} ", bulkUpdateColumnValue.Name, getValueIdentifier(bulkUpdateColumnValue));
setClauseList.Add(setClause);
}
}
if (setClauseList.Count == 0)
{
return(new questStatus(Severity.Error, "There are no columns with data specified to bulk update. At least one column value is required."));
}
for (int idx = 0; idx < setClauseList.Count; idx += 1)
{
string setClause = setClauseList[idx];
if (idx > 0)
{
sbBulkUPDATE.Append(" ,");
}
sbBulkUPDATE.AppendLine(setClause);
}
// KLUGIE: Get WHERE clause from Filter
string whereClause = null;
if (bulkUpdateRequest.Filter.SQL.IndexOf("WHERE") > -1)
{
// NOTE: NEED SUBCLAUSES PARSED OUT; THIS IS A QUICK KLUGIE FOR SINGLE TABLE ONLY. BUT, MANY THINGS CAN COME AFTER A WHERE CLAUSE.
// WE'LL FIND OUT WHAT WHEN THE SQL HERE BOMBS OUT.
whereClause = bulkUpdateRequest.Filter.SQL.Substring(bulkUpdateRequest.Filter.SQL.IndexOf("WHERE"));
// Shamelessly strip off table identifer. SINGLE TABLE ---HAS--- TO BE [T1]. TABLE ALIAS.
// I SHOULD BE ASHAMED OF THIS, BUT I'M NOT. GET 'ER DONE.
whereClause = whereClause.Replace("[T1].", "");
}
if (whereClause != null)
{
sbBulkUPDATE.AppendLine(whereClause);
}
// String it and return it. Pray for the best.
bulkUpdateRequest.SQL = sbBulkUPDATE.ToString();
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus ExecuteFilter(RunFilterRequest runFilterRequest, out ResultsSet resultsSet)
{
// Initialize
questStatus status = null;
resultsSet = null;
// Get the filter
FilterId filterId = new FilterId(runFilterRequest.FilterId.Id);
Filter filter = null;
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
status = dbFilterMgr.GetFilter(filterId, out filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// If no SQL, return.
if (string.IsNullOrEmpty(filter.SQL))
{
return(new questStatus(Severity.Error, "Filter has no SQL"));
}
// Read the tableset
TablesetId tablesetId = new TablesetId(filter.TablesetId);
Tableset tableset = null;
DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this.UserSession);
status = dbTablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Read the database
DatabaseId databaseId = new DatabaseId(tableset.DatabaseId);
Quest.Functional.MasterPricing.Database database = null;
DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this.UserSession);
status = dbDatabasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Execute SQL.
try {
status = executeSQL(runFilterRequest, database, filter, out resultsSet);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Error, String.Format("EXCEPTION: executing filter SQL: {0}", ex.Message)));
}
// klugie: Get number of FROM entities
string FROMClause = null;
List <FilterEntity> FROMEntityList = null;
List <JoinEntity> joinEntityList = null;
DbFilterSQLMgr dbFilterSQLMgr = new DbFilterSQLMgr();
status = dbFilterSQLMgr.GetFROMEntities(filter, out FROMClause, out FROMEntityList, out joinEntityList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
int numItemEntities = FROMEntityList.Count + joinEntityList.Count;
// Append lookup or type list Id's to result columns with lookups.
// NOTE: Lookups and typeList are mutually exclusive.
FilterItem filterItem = null;
try {
for (int idx = 0; idx < filter.FilterItemList.Count; idx += 1)
{
filterItem = filter.FilterItemList[idx];
string columnIdentifier = null;
FilterColumn filterColumn = null;
status = GetResultsColumnIdentifier(filter, filterItem, numItemEntities, out columnIdentifier, out filterColumn);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
if (columnIdentifier == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: columnIdentifier is NULL for filterItem {0} FilterId: {1}",
filterItem.Id, filterItem.FilterId)));
}
if (filterColumn == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: filterColumn is NULL for filterItem {0} FilterId: {1}",
filterItem.Id, filterItem.FilterId)));
}
if (!string.IsNullOrEmpty(filterItem.Label))
{
resultsSet.ResultColumns[columnIdentifier].Name = filterColumn.TablesetColumn.Name;
resultsSet.ResultColumns[columnIdentifier].Label = filterItem.Label;
}
if (filterItem.LookupId.HasValue)
{
resultsSet.ResultColumns[columnIdentifier].LookupId = filterItem.LookupId;
}
if (filterItem.TypeListId.HasValue)
{
resultsSet.ResultColumns[columnIdentifier].TypeListId = filterItem.TypeListId;
}
resultsSet.ResultColumns[columnIdentifier].bIsHidden = filterItem.bHidden;
}
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Error, String.Format("EXCEPTION: building filter results set with FilterItem {0}: {1}",
filterItem.Id, ex.Message)));
}
return(new questStatus(Severity.Success));
}
