public WorkQueueItem GetWork()
{
Int64 workQueueItemId = 0;
WorkQueueItem workQueueItem = null;
WorkQueueGetWorkUserView getWorkUserView = null;
WorkQueueView workQueueView = null;
List <Data.FilterDescriptor> filters = new List <Data.FilterDescriptor> ();
String customFilters = String.Empty;
// GET WORK IS DYNAMICALLY CREATED BY WORK QUEUE VIEWS, THIS CANNOT BE DONE IN A STORED PROCEDURE
if (!HasWorkPermission)
{
application.SetLastException(new ApplicationException("Permission Denied. No Work Permissions to Work Queue (\"" + Name + "\").")); return(null);
}
try {
// ATTEMPT TO GET USER SPECIFIC VIEW FIRST
getWorkUserView = GetWorkUserView(application.Session.SecurityAuthorityId, application.Session.UserAccountId);
if (getWorkUserView != null)
{
workQueueView = getWorkUserView.WorkQueueView;
}
// ELSE FALLBACK TO DEFAULT VIEW FOR THE OVERALL WORK QUEUE
else
{
workQueueView = application.WorkQueueViewGet(getWorkViewId);
} // 0 VALUE IS HANDLED IN THE GET FUNCTION
// FINALLY FALLBACK TO SYSTEM DEFAULT VIEW IF NO VIEW ASSIGNED TO WORK QUEUE
if (workQueueView != null)
{
// APPEND FILTERS THAT ARE FOR STANDARD FIELDS AND NOT CUSTOM FIELDS
foreach (Int32 currentFilterKey in workQueueView.FilterDefinitions.Keys)
{
if (workQueueView.WellKnownFields.ContainsKey(workQueueView.FilterDefinitions[currentFilterKey].PropertyPath))
{
filters.Add((Data.FilterDescriptor)workQueueView.FilterDefinitions[currentFilterKey]);
}
}
}
StringBuilder selectStatement = new StringBuilder();
selectStatement.Append("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE \r\n\r\n");
selectStatement.Append("BEGIN TRANSACTION \r\n\r\n");
selectStatement.Append(" DECLARE @workQueueItemId AS BIGINT \r\n\r\n");
selectStatement.Append(" DECLARE @itemGroupKey AS VARCHAR (060) \r\n\r\n");
selectStatement.Append(" SELECT @workQueueItemId = WorkQueueItemId, @itemGroupKey = ItemGroupKey \r\n\r\n FROM (\r\n\r\n");
selectStatement.Append(" SELECT \r\n\r\n");
selectStatement.Append(" " + application.WorkQueueItemsGetRowNumberSql(null, workQueueView) + " WorkQueueItemId, ItemGroupKey \r\n\r\n");
selectStatement.Append(" FROM (" + application.WorkQueueItemsGetSqlStatement(filters) + ") AS WorkQueueItem ) AS WorkQueueItemTop1 WHERE RowNumber = 1 \r\n");
if (workQueueView != null)
{
String customFields = String.Empty;
foreach (Core.Work.WorkQueueViewFieldDefinition currentFieldDefinition in workQueueView.FieldDefinitions)
{
String extendedPropertyField = currentFieldDefinition.SqlSelectList;
if (!String.IsNullOrEmpty(extendedPropertyField))
{
customFields = customFields + ", " + extendedPropertyField;
}
}
selectStatement.Replace("/*_CUSTOM_FIELD_INSERT_*/", customFields);
}
String itemFilter = " AND (WorkQueueItem.AssignedToSecurityAuthorityId = 0) AND (CompletionDate IS NULL) \r\n\r\n";
itemFilter = itemFilter + " AND (GETDATE () >= WorkQueueItem.ConstraintDate)\r\n\r\n";
itemFilter = itemFilter + " AND (WorkQueueId = " + Id.ToString() + ")\r\n\r\n";
itemFilter = itemFilter + " AND ((WorkTimeRestrictions IS NULL) \r\n\r\n";
itemFilter = itemFilter + " OR (CAST ((ISNULL (WorkTimeRestrictions.value ('(DayOfWeekTimes/";
itemFilter = itemFilter + "Day[@DayOfWeek=\"" + ((Int32)DateTime.Today.DayOfWeek).ToString() + "\"]/";
itemFilter = itemFilter + "Time[@StartTime <= \"" + DateTime.Now.ToString("hh:mm:ss") + "\"";
itemFilter = itemFilter + "and @EndTime >= \"" + DateTime.Now.ToString("hh:mm:ss") + "\"])[1]', 'BIT'), 1) - 1) AS BIT) = 1)) \r\n\r\n";
if (workQueueView != null)
{
// APPEND FILTERS THAT ARE NOT STANDARD FIELDS
foreach (Int32 currentFilterKey in workQueueView.FilterDefinitions.Keys)
{
if (!workQueueView.WellKnownFields.ContainsKey(workQueueView.FilterDefinitions[currentFilterKey].PropertyPath))
{
foreach (Core.Work.WorkQueueViewFieldDefinition currentFieldDefinition in workQueueView.FieldDefinitions)
{
if (currentFieldDefinition.DisplayName == workQueueView.FilterDefinitions[currentFilterKey].PropertyPath)
{
Data.FilterDescriptor filterDescriptor = new Data.FilterDescriptor(
currentFieldDefinition.SqlDeclaration,
workQueueView.FilterDefinitions[currentFilterKey].Operator,
workQueueView.FilterDefinitions[currentFilterKey].Parameter.Value);
itemFilter = itemFilter + " AND (" + filterDescriptor.SqlCriteriaString(String.Empty) + ")";
}
}
}
}
}
selectStatement.Replace("/*_CUSTOM_FILTER_INSERT_*/", itemFilter);
selectStatement.Append(" IF (@workQueueItemId IS NOT NULL) \r\n\r\n");
selectStatement.Append(" BEGIN \r\n\r\n");
selectStatement.Append(" EXEC WorkQueueItem_AssignTo @workQueueItemId, ");
selectStatement.Append(application.Session.SecurityAuthorityId.ToString() + ", ");
selectStatement.Append("'" + application.Session.UserAccountId.Replace("'", "''") + "', ");
selectStatement.Append("'" + application.Session.UserAccountName.Replace("'", "''") + "', ");
selectStatement.Append("'" + application.Session.UserDisplayName.Replace("'", "''") + "', ");
selectStatement.Append("'Get Work', \r\n\r\n");
selectStatement.Append("'" + application.Session.SecurityAuthorityName.Replace("'", "''") + "', ");
selectStatement.Append("'" + application.Session.UserAccountId.Replace("'", "''") + "', ");
selectStatement.Append("'" + application.Session.UserAccountName.Replace("'", "''") + "' \r\n\r\n");
if (getWorkUseGrouping)
{
selectStatement.Append(" IF (LEN (RTRIM (@itemGroupKey)) > 0) \r\n\r\n");
selectStatement.Append(" BEGIN \r\n\r\n");
selectStatement.Append(" DECLARE @currentWorkQueueItemId AS BIGINT \r\n\r\n");
selectStatement.Append(" DECLARE GroupItemsCursor CURSOR FOR \r\n\r\n");
selectStatement.Append(" SELECT WorkQueueItemId FROM WorkQueueItem WHERE (WorkQueueId = " + Id.ToString() + ") AND (ItemGroupKey = @itemGroupKey) AND (WorkQueueItemId <> @workQueueItemId) AND CompletionDate IS NULL AND AssignedToSecurityAuthorityId = 0 \r\n\r\n");
selectStatement.Append(" OPEN GroupItemsCursor \r\n\r\n");
selectStatement.Append(" FETCH NEXT FROM GroupItemsCursor INTO @currentWorkQueueItemId \r\n\r\n");
selectStatement.Append(" WHILE (@@FETCH_STATUS = 0) \r\n\r\n");
selectStatement.Append(" BEGIN \r\n\r\n");
selectStatement.Append(" EXEC WorkQueueItem_AssignTo @currentWorkQueueItemId, ");
selectStatement.Append(application.Session.SecurityAuthorityId.ToString() + ", ");
selectStatement.Append("'" + application.Session.UserAccountId.Replace("'", "''") + "', ");
selectStatement.Append("'" + application.Session.UserAccountName.Replace("'", "''") + "', ");
selectStatement.Append("'" + application.Session.UserDisplayName.Replace("'", "''") + "', ");
selectStatement.Append("'Get Work - By Grouping', \r\n\r\n");
selectStatement.Append("'" + application.Session.SecurityAuthorityName.Replace("'", "''") + "', ");
selectStatement.Append("'" + application.Session.UserAccountId.Replace("'", "''") + "', ");
selectStatement.Append("'" + application.Session.UserAccountName.Replace("'", "''") + "' \r\n\r\n");
selectStatement.Append(" FETCH NEXT FROM GroupItemsCursor INTO @currentWorkQueueItemId \r\n\r\n");
selectStatement.Append(" END \r\n\r\n");
selectStatement.Append(" CLOSE GroupItemsCursor \r\n\r\n");
selectStatement.Append(" DEALLOCATE GroupItemsCursor \r\n\r\n");
selectStatement.Append(" END \r\n\r\n");
}
selectStatement.Append(" END \r\n\r\n");
selectStatement.Append(" ELSE \r\n\r\n");
selectStatement.Append(" BEGIN \r\n\r\n");
selectStatement.Append(" SET @workQueueItemId = 0 \r\n\r\n");
selectStatement.Append(" END \r\n\r\n");
selectStatement.Append(" SELECT @workQueueItemId \r\n\r\n");
selectStatement.Append("COMMIT TRANSACTION \r\n\r\n");
workQueueItemId = (Int64)application.EnvironmentDatabase.ExecuteScalar(selectStatement.ToString());
workQueueItem = application.WorkQueueItemGet(workQueueItemId);
}
catch (Exception applicationException) {
application.EnvironmentDatabase.ExecuteSqlStatement("ROLLBACK TRANSACTION");
throw applicationException;
}
finally {
}
return(workQueueItem);
}
public WorkQueueItem GetWorkOld()
{
Int64 workQueueItemId = 0;
WorkQueueItem workQueueItem = null;
System.Data.SqlClient.SqlCommand getWorkCommand = null;
if (!HasWorkPermission)
{
application.SetLastException(new ApplicationException("Permission Denied. No Work Permissions to Work Queue (\"" + Name + "\").")); return(null);
}
try {
getWorkCommand = application.EnvironmentDatabase.CreateSqlCommand("WorkQueue_GetWork");
getWorkCommand.CommandType = System.Data.CommandType.StoredProcedure;
getWorkCommand.Parameters.Add("@workQueueId", System.Data.SqlDbType.BigInt);
getWorkCommand.Parameters["@workQueueId"].Value = Id;
getWorkCommand.Parameters.Add("@securityAuthorityId", System.Data.SqlDbType.BigInt);
getWorkCommand.Parameters["@securityAuthorityId"].Value = application.Session.SecurityAuthorityId;
getWorkCommand.Parameters.Add("@securityAuthorityName", System.Data.SqlDbType.VarChar);
getWorkCommand.Parameters["@securityAuthorityName"].Value = application.Session.SecurityAuthorityName;
getWorkCommand.Parameters.Add("@securityAccountId", System.Data.SqlDbType.VarChar);
getWorkCommand.Parameters["@securityAccountId"].Value = application.Session.UserAccountId;
getWorkCommand.Parameters.Add("@securityAccountName", System.Data.SqlDbType.VarChar);
getWorkCommand.Parameters["@securityAccountName"].Value = application.Session.UserAccountName;
getWorkCommand.Parameters.Add("@securityUserDisplayName", System.Data.SqlDbType.VarChar);
getWorkCommand.Parameters["@securityUserDisplayName"].Value = application.Session.UserDisplayName;
getWorkCommand.Parameters.Add("@workQueueItemId", System.Data.SqlDbType.BigInt);
getWorkCommand.Parameters["@workQueueItemId"].Direction = System.Data.ParameterDirection.Output;
application.EnvironmentDatabase.OnDemandOpen();
if (getWorkCommand.ExecuteNonQuery() != 0)
{
workQueueItemId = (Int64)getWorkCommand.Parameters["@workQueueItemId"].Value;
if (workQueueItemId != 0)
{
workQueueItem = new WorkQueueItem(application, workQueueItemId);
}
}
}
catch (Exception applicationException) {
throw applicationException;
}
finally {
if (getWorkCommand != null)
{
getWorkCommand.Dispose();
getWorkCommand = null;
}
application.EnvironmentDatabase.OnDemandClose();
}
return(workQueueItem);
}