/// <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; }