/// <summary>
/// Fetches reverse cross ref list.
/// </summary>
/// <param name="processGuid">The process GUID.</param>
/// <returns>The <see cref="IList" />.</returns>
public IList<PublishedProcessInfoDTO> FetchReverseCrossRefList(Guid processGuid)
{
var result = new List<PublishedProcessInfoDTO>();
const string CmdText =
@"
-- Select processes with cross reference fields
DECLARE @pp_id INT
SELECT @pp_id = pp.[Id] FROM [dbo].[PublishedProcesses] pp WHERE pp.[ProcessGuid] = @processGuid
SELECT pp.[Id]
,p.[Name]
,p.[SystemName]
,p.[Id]
,p.[IsStateEnabled]
,p.[ProcessOption]
FROM [dbo].[PublishedProcesses] pp
INNER JOIN [dbo].[Processes] p ON p.[Id] = pp.[ProcessId]
INNER JOIN (SELECT DISTINCT s.[ProcessId] AS [Id]
FROM [dbo].[Sections] s
INNER JOIN [dbo].[Fields] f on f.[SectionId] = s.[Id]
INNER JOIN [dbo].[CrossRefRequredFieldStep] x on x.[FieldId] = f.[Id]
WHERE x.[CrossRefProcessId] = @pp_id) pu ON p.[Id] = pu.[Id]
WHERE pp.Id <> @pp_id
-- Select processes with checklist fields
DECLARE @pp_systemName AS NVARCHAR(200)
SELECT @pp_systemName = p.[SystemName] FROM [dbo].[Processes] p WHERE p.[Guid] = @processGuid AND p.[IsPublishedCopy] = 1 AND p.[IsRemoved] = 0
SELECT pp.[Id]
,p.[Name]
,p.[SystemName]
,p.[Id]
,p.[IsStateEnabled]
,p.[ProcessOption]
FROM [dbo].[PublishedProcesses] pp
INNER JOIN [dbo].[Processes] p ON p.[Id] = pp.[ProcessId]
INNER JOIN (SELECT DISTINCT s.[ProcessId] AS [Id]
FROM [dbo].[Sections] s
INNER JOIN [dbo].[Fields] f on f.[SectionId] = s.[Id]
INNER JOIN [dbo].[stepChecklist] x on x.[FieldId] = f.[Id]
WHERE x.[AnswerProcessSystemName] = @pp_systemName) pu ON p.[Id] = pu.[Id]
WHERE pp.Id <> @pp_id";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(CmdText, cn))
{
cmd.Parameters.AddWithValue("@processGuid", processGuid);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
do
{
while (reader.Read())
{
var dto = new PublishedProcessInfoDTO
{
Id = reader.GetInt(0),
Name = reader.GetString(1),
SystemName = reader.GetString(2),
ProcessId = reader.GetInt(3),
IsStateEnabled = reader.GetBool(4),
ProcessOption = reader.GetEnum(5, ProcessOption.None)
};
if (result.All(x => x.Id != dto.Id))
{
result.Add(dto);
}
}
}
while (reader.NextResult());
}
}
}
return result;
}
/// <summary>
/// Retrieves published process by process id.
/// </summary>
/// <param name="processId">The process id.</param>
/// <returns>The <see cref="PublishedProcessInfoDTO" />.</returns>
public PublishedProcessInfoDTO FetchPublishedProcessByProcessId(int processId)
{
var result = new PublishedProcessInfoDTO();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
const string Sql = @"
SELECT pp.Id
,Name
,p.Id AS ProcessId
,p.SystemName
,p.BaseProcessId
FROM [dbo].[PublishedProcesses] pp
INNER JOIN Processes p
ON pp.ProcessId = p.Id
WHERE p.id = @processId";
using (var cmd = new SqlCommand(Sql, cn))
{
cmd.Parameters.AddWithValue("@processId", processId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
result = new PublishedProcessInfoDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
ProcessId = reader.GetInt32(2),
SystemName = reader.GetString(3)
};
if (!reader.IsDBNull(4))
{
result.BaseProcess = this.FetchPublishedProcess(reader.GetInt32(4));
}
}
}
}
}
return result;
}
/// <summary>
/// Retrieves published process by process name.
/// </summary>
/// <param name="processName">The process name.</param>
/// <returns>The <see cref="PublishedProcessInfoDTO" />.</returns>
public PublishedProcessInfoDTO FetchPublishedProcess(string processName)
{
var result = new PublishedProcessInfoDTO();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
const string Sql =
@"
SELECT pp.Id
,Name
,p.Id AS ProcessId
,p.SystemName
,p.BaseProcessId
,p.ProcessOption
,p.SimpleProcess
,p.IsStateEnabled
FROM [dbo].[PublishedProcesses] pp
INNER JOIN Processes p ON pp.ProcessId = p.Id
WHERE p.SystemName = @procName AND p.IsRemoved=0";
using (var cmd = new SqlCommand(Sql, cn))
{
cmd.Parameters.AddWithValue("@procName", processName);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
result = new PublishedProcessInfoDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
ProcessId = reader.GetInt32(2),
SystemName = reader.GetString(3),
ProcessOption =
!string.IsNullOrEmpty(reader.GetString(5))
? (ProcessOption)
Enum.Parse(
typeof(ProcessOption), reader.GetString(5))
: ProcessOption.None,
SimpleProcess = reader.GetBool(6),
IsStateEnabled = reader.GetBoolean(7)
};
if (!reader.IsDBNull(4))
{
result.BaseProcess = this.FetchPublishedProcess(reader.GetInt32(4));
}
}
}
}
}
return result;
}
public PublishedProcessInfoDTO FetchPublishedProcess(int id)
{
var result = new PublishedProcessInfoDTO();
var sql = string.Format(CultureInfo.InvariantCulture, @"
SELECT pp.Id
,ISNULL(pl.ProcessName, p.[Name])
,p.Id AS ProcessId
,p.SystemName
,p.BaseProcessId
,p.ProcessOption
,p.SimpleProcess
,p.IsStateEnabled
FROM [dbo].[PublishedProcesses] pp
INNER JOIN Processes p ON pp.ProcessId = p.Id
INNER JOIN Processes p2 ON pp.ProcessGuid = p2.[Guid] and p2.[IsPublishedCopy] = 0
LEFT OUTER JOIN dbo.Localizations l ON l.CultureName = '{0}'
LEFT OUTER JOIN dbo.ProcessLocalizations pl ON pl.LocalizationId = l.Id AND p2.Id = pl.ProcessId
WHERE pp.id = @id", System.Threading.Thread.CurrentThread.CurrentUICulture.Name);
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(sql, cn))
{
cmd.Parameters.AddWithValue("@id", id);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
result = new PublishedProcessInfoDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
ProcessId = reader.GetInt32(2),
SystemName = reader.GetString(3),
ProcessOption =
!string.IsNullOrEmpty(reader.GetString(5))
? (ProcessOption)
Enum.Parse(
typeof(ProcessOption), reader.GetString(5))
: ProcessOption.None,
SimpleProcess = reader.GetBool(6),
IsStateEnabled = reader.GetBoolean(7)
};
if (!reader.IsDBNull(4))
{
result.BaseProcess = this.FetchPublishedProcess(reader.GetInt32(4));
}
}
}
}
}
return result;
}
/// <summary>
/// Retrieves synchronized processes.
/// </summary>
/// <returns>The <see cref="IList" />.</returns>
public IEnumerable<PublishedProcessInfoDTO> FetchSynchronizedProcesses()
{
var result = new List<PublishedProcessInfoDTO>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
const string CommandText = @"
SELECT pp.Id
,p.[Name]
,p.[SystemName]
,p.Id AS ProcessId
,p.[BaseProcessId]
,p.[IconId]
FROM [dbo].[PublishedProcesses] pp
INNER JOIN Processes p
ON pp.ProcessId = p.Id
WHERE p.IsRemoved = 0 AND p.IsInactive = 0 AND EXISTS(
SELECT *
FROM [dbo].[SyncProcesses] sp
WHERE sp.ProcessId = p.Id)
ORDER BY
p.Name
";
using (var cmd = new SqlCommand(CommandText, connection))
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var dto = new PublishedProcessInfoDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
SystemName = reader.GetString(2),
ProcessId = reader.GetInt32(3),
IconId = reader.GetNullableInt(5)
};
if (!reader.IsDBNull(4))
{
dto.BaseProcess = this.FetchPublishedProcess(reader.GetInt(4));
}
result.Add(dto);
}
}
}
return result;
}
public IList<PublishedProcessInfoDTO> FetchPublishedProcesses(string culture)
{
var result = new List<PublishedProcessInfoDTO>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
var commandText = string.Format(CultureInfo.InvariantCulture, @"
SELECT pp.Id
,ISNULL(pl.ProcessName, p.[Name])
,p.[SystemName]
,p.Id AS ProcessId
,p.[BaseProcessId]
,p2.[IconId]
,p.[Guid]
,p.[ProcessOption]
,p.[IsStateEnabled]
FROM [dbo].[PublishedProcesses] pp
INNER JOIN Processes p ON pp.ProcessId = p.Id
INNER JOIN Processes p2 ON pp.ProcessGuid = p2.[Guid] and p2.[IsPublishedCopy] = 0
LEFT OUTER JOIN dbo.ProcessLocalizations pl
INNER JOIN dbo.Localizations l ON pl.LocalizationId = l.Id AND l.CultureName = '{0}'
ON p2.Id = pl.ProcessId
WHERE p.IsRemoved = 0 AND p2.IsRemoved = 0
ORDER BY p.Name", culture);
using (var cmd = new SqlCommand(commandText, connection))
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var dto = new PublishedProcessInfoDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
SystemName = reader.GetString(2),
ProcessId = reader.GetInt32(3),
IconId = reader.GetNullableInt(5),
ProcessGuid = reader.GetGuid(6),
ProcessOption = reader.GetEnum(7, ProcessOption.None),
IsStateEnabled = reader.GetBoolean(8)
};
if (!reader.IsDBNull(4))
{
dto.BaseProcess = this.FetchPublishedProcess(reader.GetInt(4));
}
result.Add(dto);
}
}
}
return result;
}