public async Task QueryAll(Guid configurationId, Func <CommonSignConfigurationRootAction, Task> callback)
{
var resultList = new List <CommonSignConfigurationRootAction>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _dbConnectionFactory.CreateReadForWorkflow(), async (conn, transaction) =>
{
long?sequence = null;
const int pageSize = 1000;
while (true)
{
resultList.Clear();
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (var command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
})
{
if (!sequence.HasValue)
{
command.CommandText = string.Format(@"SELECT TOP (@pagesize) {0},{1},{2},{3}
FROM [CommonSignConfigurationRootAction] AS rootaction
INNER JOIN [CommonSignConfiguration] AS config
ON rootaction.configurationid = config.id
LEFT JOIN [CommonSignConfigurationNode] AS node
ON rootaction.entrynodeid = node.id
LEFT JOIN [CommonSignConfiguration] AS nodeconfig
ON nodeconfig.id = node.configurationid
WHERE rootaction.configurationid = @configurationid
ORDER BY rootaction.sequence;",
StoreHelper.GetCommonSignConfigurationRootActionSelectFields("rootaction"),
StoreHelper.GetCommonSignConfigurationSelectFields("config"),
StoreHelper.GetCommonSignConfigurationNodeSelectFields("node"),
StoreHelper.GetCommonSignConfigurationSelectFields("nodeconfig"));
}
else
{
command.CommandText = string.Format(@"SELECT TOP (@pagesize) {0},{1},{2},{3}
FROM [CommonSignConfigurationRootAction] AS rootaction
INNER JOIN [CommonSignConfiguration] AS config
ON rootaction.configurationid = config.id
LEFT JOIN [CommonSignConfigurationNode] AS node
ON rootaction.entrynodeid = node.id
LEFT JOIN [CommonSignConfiguration] AS nodeconfig
ON nodeconfig.id = node.configurationid
WHERE rootaction.configurationid = @configurationid
AND rootaction.sequence > @sequence
ORDER BY rootaction.sequence;",
StoreHelper.GetCommonSignConfigurationRootActionSelectFields("rootaction"),
StoreHelper.GetCommonSignConfigurationSelectFields("config"),
StoreHelper.GetCommonSignConfigurationNodeSelectFields("node"),
StoreHelper.GetCommonSignConfigurationSelectFields("nodeconfig"));
}
var parameter = new SqlParameter("@configurationid", SqlDbType.UniqueIdentifier)
{
Value = configurationId
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@pagesize", SqlDbType.Int)
{
Value = pageSize
};
command.Parameters.Add(parameter);
if (sequence.HasValue)
{
parameter = new SqlParameter("@sequence", SqlDbType.BigInt)
{
Value = sequence
};
command.Parameters.Add(parameter);
}
await command.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var data = new CommonSignConfigurationRootAction();
StoreHelper.SetCommonSignConfigurationRootActionSelectFields(data, reader, "rootaction");
data.Configuration = new CommonSignConfiguration();
StoreHelper.SetCommonSignConfigurationSelectFields(data.Configuration, reader, "config");
if (reader[string.Format("{0}id", "node")] != DBNull.Value)
{
data.EntryNode = new CommonSignConfigurationNode();
StoreHelper.SetCommonSignConfigurationNodeSelectFields(data.EntryNode, reader, "node");
}
if (reader[string.Format("{0}id", "nodeconfig")] != DBNull.Value)
{
data.EntryNode.Configuration = new CommonSignConfiguration();
StoreHelper.SetCommonSignConfigurationSelectFields(data.EntryNode.Configuration, reader, "nodeconfig");
}
sequence = (long)reader["rootactionsequence"];
resultList.Add(data);
}
await reader.CloseAsync();
}
}
foreach (var workflowStep in resultList)
{
await callback(workflowStep);
}
if (resultList.Count != pageSize)
{
break;
}
}
});
}
public async Task <CommonSignConfigurationRootAction> QueryByActionName(Guid configurationId, string actionName)
{
CommonSignConfigurationRootAction result = null;
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _dbConnectionFactory.CreateReadForWorkflow(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (var command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = string.Format(@"SELECT {0},{1},{2},{3}
FROM [CommonSignConfigurationRootAction] AS rootaction
INNER JOIN [CommonSignConfiguration] AS config
ON rootaction.configurationid = config.id
LEFT JOIN [CommonSignConfigurationNode] AS node
ON rootaction.entrynodeid = node.id
LEFT JOIN [CommonSignConfiguration] AS nodeconfig
ON nodeconfig.id = node.configurationid
WHERE rootaction.configurationid = @configurationid
AND rootaction.actionname = @actionname
ORDER BY rootaction.sequence DESC; ",
StoreHelper.GetCommonSignConfigurationRootActionSelectFields("rootaction"),
StoreHelper.GetCommonSignConfigurationSelectFields("config"),
StoreHelper.GetCommonSignConfigurationNodeSelectFields("node"),
StoreHelper.GetCommonSignConfigurationSelectFields("nodeconfig"))
})
{
var parameter = new SqlParameter("@configurationid", SqlDbType.UniqueIdentifier)
{
Value = configurationId
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@actionname", SqlDbType.NVarChar, 500)
{
Value = actionName
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await command.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
result = new CommonSignConfigurationRootAction();
StoreHelper.SetCommonSignConfigurationRootActionSelectFields(result, reader, "rootaction");
result.Configuration = new CommonSignConfiguration();
StoreHelper.SetCommonSignConfigurationSelectFields(result.Configuration, reader, "config");
if (reader[string.Format("{0}id", "node")] != DBNull.Value)
{
result.EntryNode = new CommonSignConfigurationNode();
StoreHelper.SetCommonSignConfigurationNodeSelectFields(result.EntryNode, reader, "node");
}
if (reader[string.Format("{0}id", "nodeconfig")] != DBNull.Value)
{
result.EntryNode.Configuration = new CommonSignConfiguration();
StoreHelper.SetCommonSignConfigurationSelectFields(result.EntryNode.Configuration, reader, "nodeconfig");
}
}
await reader.CloseAsync();
}
}
});
return(result);
}
public async Task QueryByResource(string resourceType, string resourceKey, Guid resourceId, Func <WorkflowStepUserAction, Task> callback)
{
var dbInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _hashGroupName, resourceType, resourceKey);
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowStepUserAction, out string tableNameStepUserAction))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowStepUserAction
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowStep, out string tableNameStep))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowStep
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowResource, out string tableNameResource))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowResource
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
List <WorkflowStepUserAction> listAction = new List <WorkflowStepUserAction>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, dbInfo.DBConnectionNames.Read, async (conn, transaction) =>
{
int sequence = 0;
int pageSize = 500;
while (true)
{
listAction.Clear();
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
CommandText = string.Format(@"SELECT {0}
FROM {1} AS action
INNER JOIN {2} AS step ON action.stepid=step.id
INNER JOIN {3} AS resource ON resource.id = step.resourceid
WHERE resource.id=@resourceId ORDER BY action.sequence OFFSET @sequence ROWS FETCH NEXT @pagesize ROWS ONLY;", StoreHelper.GetWorkflowStepUserActionStoreSelectFields("action")
, tableNameStepUserAction, tableNameStep, tableNameResource),
Transaction = sqlTran
})
{
var parameter = new SqlParameter("@resourceId", SqlDbType.UniqueIdentifier)
{
Value = resourceId
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@pagesize", SqlDbType.Int)
{
Value = pageSize
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@sequence", SqlDbType.Int)
{
Value = sequence
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var workflowStepUserAction = new WorkflowStepUserAction();
StoreHelper.SetWorkflowStepUserActionStoreSelectFields(workflowStepUserAction, reader, "action");
listAction.Add(workflowStepUserAction);
}
await reader.CloseAsync();
}
}
foreach (var actionItem in listAction)
{
await callback(actionItem);
}
if (listAction.Count != pageSize)
{
break;
}
else
{
sequence += listAction.Count;
}
}
});
}
/// <summary>
/// 查询指定步骤下面指定用户信息的用户动作
/// </summary>
/// <param name="resourceType"></param>
/// <param name="resourceKey"></param>
/// <param name="stepId"></param>
/// <param name="userKey"></param>
/// <returns></returns>
public async Task <WorkflowStepUserAction> QueryByStepAndUser(string resourceType, string resourceKey, Guid stepId, string userKey)
{
var dbInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _hashGroupName, resourceType, resourceKey);
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowStepUserAction, out string tableNameStepUserAction))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowStepUserAction
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowStep, out string tableNameStep))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowStep
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowResource, out string tableNameResource))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowResource
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
WorkflowStepUserAction result = null;
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, dbInfo.DBConnectionNames.Read, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
CommandText = string.Format(@"SELECT {0} FROM {1} WHERE stepid =@stepid AND userkey=@userkey ;", StoreHelper.GetWorkflowStepUserActionStoreSelectFields(string.Empty), tableNameStepUserAction),
Transaction = sqlTran
})
{
var parameter = new SqlParameter("@stepid", SqlDbType.UniqueIdentifier)
{
Value = stepId
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@userkey", SqlDbType.NVarChar, 256)
{
Value = userKey
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await command.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
result = new WorkflowStepUserAction();
StoreHelper.SetWorkflowStepUserActionStoreSelectFields(result, reader, string.Empty);
}
await reader.CloseAsync();
}
}
});
return(result);
}
/// <summary>
/// 查询指定步骤下的所有用户动作
/// </summary>
/// <param name="resourceType"></param>
/// <param name="resourceKey"></param>
/// <param name="stepId"></param>
/// <param name="callback"></param>
/// <returns></returns>
public async Task QueryByStep(string resourceType, string resourceKey, Guid stepId, Func <WorkflowStepUserAction, Task> callback)
{
var dbInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _hashGroupName, resourceType, resourceKey);
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowStepUserAction, out string tableNameStepUserAction))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName
, HashEntityNames.WorkflowStepUserAction
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
List <WorkflowStepUserAction> listAction = new List <WorkflowStepUserAction>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, dbInfo.DBConnectionNames.Read, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
CommandText = string.Format(@"SELECT {0} FROM {1} WHERE stepid =@stepid ORDER BY sequence", StoreHelper.GetWorkflowStepUserActionStoreSelectFields(string.Empty), tableNameStepUserAction),
Transaction = sqlTran
})
{
var parameter = new SqlParameter("@stepid", SqlDbType.UniqueIdentifier)
{
Value = stepId
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var workflowStepUserAction = new WorkflowStepUserAction();
StoreHelper.SetWorkflowStepUserActionStoreSelectFields(workflowStepUserAction, reader, string.Empty);
listAction.Add(workflowStepUserAction);
}
await reader.CloseAsync();
}
}
foreach (var actionItem in listAction)
{
await callback(actionItem);
}
});
}
/// <summary>
/// 根据type和Key查询
/// </summary>
/// <param name="type"></param>
/// <param name="key"></param>
/// <returns></returns>
public async Task <WorkflowResource> QueryByKey(string type, string key)
{
var dbInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _hashGroupName, type, key);
if (!dbInfo.TableNames.TryGetValue(HashEntityNames.WorkflowResource, out string tableName))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName, HashEntityNames.WorkflowResource
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
WorkflowResource result = null;
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, dbInfo.DBConnectionNames.Read, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (var command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = string.Format(@"SELECT {0}
FROM {1}
WHERE [type] = @type
AND [key] = @key
ORDER BY createtime DESC ", StoreHelper.GetWorkflowResourceStoreSelectFields(string.Empty),
tableName)
})
{
var parameter = new SqlParameter("@type", SqlDbType.NVarChar, 256)
{
Value = type
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@key", SqlDbType.NVarChar, 256)
{
Value = key
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await command.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
result = new WorkflowResource();
StoreHelper.SetWorkflowResourceStoreSelectFields(result, reader, string.Empty);
}
reader.Close();
}
}
});
return(result);
}
