private static void FetchDataVariableList(SqlConnection connection, EDCEditDto dto)
{
const string Sql =
@"
SELECT
[Id]
,[GuidId]
,[EDCId]
,[VariableName]
,[VariableSystemName]
,[VariableDataType]
FROM
[dbo].[EDCDataVariables]
WHERE [EDCId] = @edcId;
";
using (var cmd = new SqlCommand(Sql, connection))
{
cmd.Parameters.AddWithValue("@edcId", dto.Id);
try
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var dataVariableType = DataVariablesType.None;
var type = reader.GetString(5);
if (!string.IsNullOrWhiteSpace(type))
{
if (type == "Double")
{
dataVariableType = DataVariablesType.Decimal;
}
else
{
dataVariableType = !string.IsNullOrEmpty(reader.GetString(5))
? (DataVariablesType)Enum.Parse(typeof(DataVariablesType), reader.GetString(5))
: DataVariablesType.None;
}
}
var edcDataVariablesEditDto = new EDCDataVariablesEditDto
{
Id = reader.GetInt(0),
Guid = reader.GetGuid(1),
EDCId = reader.GetInt32(2),
VariableName = reader.GetString(3),
VariableSystemName = reader.GetString(4),
VariableDataType = dataVariableType
};
dto.DataVariableList.Add(edcDataVariablesEditDto);
}
}
}
catch (Exception ex)
{
throw new ArgumentException(ex.Data.ToString());
}
}
}
/// <summary>
/// Reads approval action options.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="reader">The reader.</param>
private static void ReadApprovalActionOptions(ProcessEditDto process, SafeDataReader reader)
{
reader.NextResult();
while (reader.Read())
{
var guid = reader.GetGuid(1);
var actionDto = new ProcessActionEditDto(ReadRuleGuids(reader.GetInt(0)))
{
Id = reader.GetInt(0),
Guid = guid,
Name = reader.GetString(2),
Documentation = reader.GetString(3),
Subject = reader.GetString(4),
Message = reader.GetString(5),
ActionType = (ActionTypes)Enum.Parse(typeof(ActionTypes), reader.GetString(6)),
GroupName = reader.GetString(7)
};
actionDto.ApprovalOptions = new ApprovalActionOptionsEditDto
{
ActionId = actionDto.Id,
ApprovalFieldName = reader.GetString(9),
EmailFieldName = reader.GetString(10),
SendEmail = reader.GetBoolean(11),
Obsolete = reader.GetBoolean(12),
EmailDeepLink = reader.GetBoolean(13)
};
process.Actions.Add(actionDto);
}
}
/// <summary>
/// Retrieves the process.
/// </summary>
/// <param name="id">The process id.</param>
/// <param name="fetchHistory">The fetch history.</param>
/// <param name="locId">The localization/language id.</param>
/// <returns>The processEdit DTO object <see cref="ProcessEditDto" />.</returns>
public ProcessEditDto FetchProcess(int id, IProcessFetchHistory fetchHistory = null, int locId = 0)
{
var process = new ProcessEditDto();
const string commandText = "GetProcessWithLoc";
if (fetchHistory != null)
fetchHistory.BeginLogExecuteSP();
Database.GetDataReader(
commandText,
600,
r =>
{
if (r == null || !r.Read())
{
return;
}
var sr = new SafeDataReader(r);
process.Id = sr.GetInt("Id");
process.Name = sr.GetString("Name").Trim();
process.Description = sr.GetString("Description");
process.Documentation = sr.GetString("Documentation");
process.SystemName = sr.GetString("SystemName");
process.IconId = sr.GetNullableInt("IconId");
process.Guid = sr.GetGuid("Guid");
process.IsPublishedCopy = sr.GetBool("IsPublishedCopy");
process.BaseProcessId = sr.GetNullableInt("BaseProcessId");
process.BaseProcessProcessId = sr.GetNullableInt("BaseProcessProcessId");
process.ProcessOption = sr.GetString("ProcessOption");
process.IsStateEnabled = sr.GetBool("IsStateEnabled", true);
process.DefaultStateId = sr.GetInt32("DefaultStateId");
process.AllowPaperclips = sr.GetBool("AllowPaperclips", true);
process.ColorId = sr.GetNullableInt("ColorId");
process.InheritanceContext = sr.GetString("InheritanceContext");
process.ShowDerivedProcess = sr.GetBool(Constants.ShowDerivedProcess);
process.PublishedProcessId = sr.GetInt("PublishedProcessId");
process.PublishedId = sr.GetInt("PublishedId");
process.IsSystem = sr.GetBool("IsSystem");
process.SimpleProcess = sr.GetBool("SimpleProcess");
process.IsInactive = sr.GetBool("IsInactive");
process.IsTabbedUI = sr.GetBool("IsTabbedUI");
process.IsTrackable = sr.GetBool("IsTrackable");
process.ShowSummaryPage = sr.GetBool("ShowSummaryPage");
process.AllowBatchProcessing = sr.GetBool("AllowBatchProcessing");
process.LastUpdated = sr.GetDateTime("LastUpdated");
process.IdentifierField = sr.GetString("IdentifierField");
var processVersionId = sr.GetInt("ProcessVersionId");
if (fetchHistory != null)
fetchHistory.LogExecuteSP();
if (processVersionId > 0)
{
process.Version = new VersionDto
{
ProcessId = processVersionId,
VersioningStyle = sr.GetEnum("VersioningStyle", VersioningStyles.Char),
StartingVersion = sr.GetString("StartingVersion"),
NextVersionStateGuid = sr.GetGuid("NextVersionStateGuid"),
PreviousVersionStateGuid = sr.GetGuid("PreviousVersionStateGuid"),
IncludeVersionNumberInList = sr.GetBool("IncludeVersionNumberInList"),
IncludeVersionDateInList = sr.GetBool("IncludeVersionDateInList"),
StateFilterGuid = sr.GetGuid("StateFilterGuid")
};
}
if (fetchHistory != null)
fetchHistory.ProcessHistoryDTO.FetchSectionsTime = Profiler.Profile(() => ReadSections(process, sr));
else
ReadSections(process, sr);
ReadRequiredRuleConfigs(process, sr);
if (fetchHistory != null)
fetchHistory.ProcessHistoryDTO.FetchSecurityConfigurationsTime = fetchHistory.Profile(
() =>
{
ReadProcessSecurityConfigurations(process, sr);
ReadProcessSecurityConfigs(process, sr);
});
else
{
ReadProcessSecurityConfigurations(process, sr);
ReadProcessSecurityConfigs(process, sr);
}
ReadStates(process, sr);
ReadEscalationActionOptions(process, sr);
ReadAssignmentActionOptions(process, sr);
ReadApprovalActionOptions(process, sr);
ReadActionRules(process, sr);
ReadFilters(process, sr);
ReadMetrics(process, sr);
ReadKpis(process, sr);
ReadCommands(process, sr);
ReadESyncProcesses(process, sr);
ReadReports(process, sr);
ReadProcessViews(process, sr);
ReadDataTriggers(process, sr);
ReadIntegrationServices(process, sr);
ReadSearchDisplayFields(process, sr);
ReadLayouts(process, sr);
ReadProcessDataIndexes(process, sr);
ReadExternalData(process, sr);
},
CommandType.StoredProcedure,
new SqlParameter("p_id", id),
new SqlParameter("localizationId", locId));
// run after GetProcessNew
this.ReadDependencies(process);
return process;
}
/// <summary>
/// Retrieves reverse cross ref fields.
/// </summary>
/// <param name="processId">The process id.</param>
/// <param name="referenceProcessGuid">The reference process GUID.</param>
/// <param name="includeInheritedFields">The include inherited fields.</param>
/// <returns>The <see cref="IList" />.</returns>
public IList<ReverseCrossRefFieldDto> FetchReverseCrossRefFields(int processId, Guid referenceProcessGuid = new Guid(), bool includeInheritedFields = false)
{
var result = new List<ReverseCrossRefFieldDto>();
string cmdText;
if (referenceProcessGuid == Guid.Empty)
{
if (includeInheritedFields)
{
cmdText = @"
WITH ProcessList(
Id,
PublishedProcessId,
IsBase
)
AS
(
SELECT p.Id AS Id,
pp.Id AS PublishedProcessId,
0 AS IsBase
FROM Processes p
LEFT OUTER JOIN PublishedProcesses pp ON pp.Id = p.BaseProcessId
WHERE p.Id = @processId
UNION ALL
SELECT pp.ProcessId,
pp.Id AS PublishedProcessId,
1 AS IsBase
FROM Processes p
INNER JOIN PublishedProcesses pp
ON p.BaseProcessId = pp.Id
INNER JOIN processes p1
ON pp.ProcessId = p1.Id
INNER JOIN ProcessList pl
ON p.id = pl.Id
)
SELECT f.[Name]
, f.[SystemName]
, f.[Id]
, x.[DisplayMultiple]
, x.[DisplayFieldName]
, x.[CrossRefFieldName]
, p.[SystemName]
, p.[Id]
FROM [dbo].[Sections] s
INNER JOIN ProcessList pl ON s.ProcessId = pl.Id
INNER JOIN [dbo].[Fields] f ON s.[Id] = f.[SectionId]
INNER JOIN [dbo].[ReverseCrossRefRequiredFieldStep] x ON f.[Id] = x.[FieldId]
INNER JOIN [dbo].[PublishedProcesses] [pp] ON [pp].[Id] = x.ReverseCrossRefProcessId
INNER JOIN [dbo].[Processes] [p] ON [p].[Id] = pp.ProcessId
";
}
else
{
cmdText = @"
SELECT f.[Name]
, f.[SystemName]
, f.[Id]
, x.[DisplayMultiple]
, x.[DisplayFieldName]
, x.[CrossRefFieldName]
, p.[SystemName]
, p.[Id]
FROM [dbo].[Sections] s
INNER JOIN [dbo].[Fields] f ON s.[Id] = f.[SectionId]
INNER JOIN [dbo].[ReverseCrossRefRequiredFieldStep] x ON f.[Id] = x.[FieldId]
INNER JOIN [dbo].[PublishedProcesses] [pp] ON [pp].[Id] = x.ReverseCrossRefProcessId
INNER JOIN [dbo].[Processes] [p] ON [p].[Id] = pp.ProcessId
WHERE s.[ProcessId] = @processId";
}
}
else
{
cmdText = @"
DECLARE @pp_id INT
SELECT @pp_id = pp.[Id] FROM [dbo].[PublishedProcesses] pp WHERE pp.[ProcessGuid] = @referenceProcessGuid
SELECT f.[Name]
, f.[SystemName]
, f.[Id]
, x.[DisplayMultiple]
, x.[DisplayFieldName]
, x.[CrossRefFieldName]
, p.[SystemName]
, p.[Id]
FROM [dbo].[Sections] s
INNER JOIN [dbo].[Fields] f ON s.[Id] = f.[SectionId]
INNER JOIN [dbo].[ReverseCrossRefRequiredFieldStep] x ON f.[Id] = x.[FieldId]
INNER JOIN [dbo].[PublishedProcesses] [pp] ON [pp].[Id] = x.ReverseCrossRefProcessId
INNER JOIN [dbo].[Processes] [p] ON [p].[Id] = pp.ProcessId
WHERE s.[ProcessId] = @processId AND x.[ReverseCrossRefProcessId] = @pp_id";
}
cmdText += @"
";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(cmdText, cn))
{
cmd.Parameters.AddWithValue("@processId", processId);
cmd.Parameters.AddWithValue("@referenceProcessGuid", referenceProcessGuid);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
result.Add(new ReverseCrossRefFieldDto
{
Name = reader.GetString(0),
SystemName = reader.GetString(1),
FieldId = reader.GetInt(2),
DisplayMultiple = reader.GetBoolean(3),
DisplayField = reader.GetString(4),
ReferenceField = reader.GetString(5),
ReferenceProcessSystemName = reader.GetString(6),
ReferenceProcessId = reader.GetInt(7)
});
}
}
}
}
return result;
}
/// <summary>
/// Fetches process list.
/// </summary>
/// <returns>The <see cref="IList" />.</returns>
public IList<ProcessInfoDTO> FetchProcessList(bool systemOnly = false)
{
string cmdText =
@"SELECT p.[Id], p.[SystemName],p.[Guid]
FROM [dbo].[Processes] p
where IsRemoved = 0";
if (systemOnly)
cmdText += " AND IsSystem = 1 AND IsPublishedCopy = 0";
var result = new List<ProcessInfoDTO>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(cmdText, cn))
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
result.Add(
new ProcessInfoDTO
{
Id = reader.GetInt(0),
SystemName = reader.GetString(1),
Guid = reader.GetGuid(2)
});
}
}
}
return result;
}
/// <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>
/// Reads report security configurations.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private static void ReadReportSecurityConfigurations(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
int? reportId = null;
ProcessReportEditDto report = null;
while (sr.Read())
{
var config = new ProcessReportSecurityConfigurationDto
{
Id = sr.GetInt(0),
Guid = sr.GetGuid(1),
ReportId = sr.GetInt(2),
RoleId = sr.GetInt(3),
BusinessUnitId = sr.GetInt(4),
Name = sr.GetString(5)
};
if (config.ReportId != reportId)
{
report = process.ReportList.First(r => r.Id == config.ReportId);
reportId = config.ReportId;
}
report.SecurityConfigurations.Add(config);
}
}
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;
}
/// <summary>
/// Reads connectors security configurations.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private static void ReadConnectorsSecurityConfigurations(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
var connectorInId = Guid.Empty;
var connectorOutId = Guid.Empty;
ConnectorDTO connector = null;
while (sr.Read())
{
var securityDto = new StateConnectorSecurityConfigurationEditDto
{
Id = sr.GetInt(0),
CanMove = sr.GetBoolean(1),
RoleId = sr.GetInt(2),
BusinessUnitId = sr.GetInt(3),
PersonFieldName = sr.GetString(4),
StateInGuid = sr.GetGuid(5),
StateOutGuid = sr.GetGuid(6)
};
if (connectorInId != securityDto.StateInGuid || connectorOutId != securityDto.StateOutGuid)
{
connectorInId = securityDto.StateInGuid;
connectorOutId = securityDto.StateOutGuid;
connector = process.States.SelectMany(s => s.Connectors).FirstOrDefault(c => c.ConnectsToStateGuid == connectorInId && c.ConnectsFromStateGuid == connectorOutId);
if (connector == null)
{
continue;
}
}
if (connector != null)
{
connector.SecurityConfigurations.Add(securityDto);
}
}
}
/// <summary>
/// The read eSync processes.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private static void ReadESyncProcesses(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
while (sr.Read())
{
var syncProcess = new ESyncProcessDto
{
Id = sr.GetInt(0),
Guid = sr.GetGuid(1),
Name = sr.GetString(2),
Description = sr.GetString(3),
Action = (ESyncAction)Enum.Parse(typeof(ESyncAction), sr.GetString(4), true),
Definition = sr.GetString(5),
PublishedCopyId = sr.GetNullableInt(6),
GenerateIndexes = sr.GetBoolean("GenerateIndexes")
};
process.ESyncProcessList.Add(syncProcess);
}
}
/// <summary>
/// The read security config system fields.
/// </summary>
/// <param name="sr">The reader.</param>
/// <param name="process">The process.</param>
private static void ReadSecurityConfigSystemFields(SafeDataReader sr, ProcessEditDto process)
{
sr.NextResult();
while (sr.Read())
{
var systemFieldSecurityConfig = new ProcessSystemFieldSecurityConfigEditDto
{
Id = sr.GetInt(0),
SecurityConfigId = sr.GetInt(1),
Guid = sr.GetGuid(2),
FieldSystemName = sr.GetString(3),
CanView = sr.GetBoolean(4),
CanEdit = sr.GetBoolean(5)
};
var config = process.SecurityConfigs.FirstOrDefault(sc => sc.Id == systemFieldSecurityConfig.SecurityConfigId);
if (config != null)
{
config.SecurityConfigSystemFields.Add(systemFieldSecurityConfig);
}
}
}
/// <summary>
/// Gets step list.
/// </summary>
/// <param name="sr">The reader.</param>
/// <param name="stepList">The step list.</param>
private static void GetStepList(SafeDataReader sr, List<StepDto> stepList)
{
while (sr.Read())
{
var fieldEditorDto = new StepDto
{
Id = sr.GetInt("Id"),
StepId = sr.GetInt("Id"),
Name = sr.GetString("Name"),
Class = sr.GetString("Class"),
FieldId = sr.GetInt32("FieldId"),
StepNumber = sr.GetInt("StepNumber"),
ViewClass = sr.GetString("ViewClass")
};
stepList.Add(fieldEditorDto);
}
}
/// <summary>
/// Reads fields.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private void ReadFields(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
int? sectionId = null;
SectionDto section = null;
var times = new List<double>();
while (sr.Read())
{
var start = DateTime.Now;
var fieldDto = new FieldDto
{
Id = sr.GetInt32(0),
Name = sr.GetSafeString(1, string.Empty).Replace(@"""", "''"),
FieldTypeId = sr.GetInt32(2),
SectionId = sr.GetInt32(3),
Width = sr.GetDouble(4),
RowSpan = sr.GetInt(5),
ShowInList = sr.GetBoolean(6),
IncludeInFilter = sr.GetBoolean(7),
HideFromDetails = sr.GetBoolean(8),
SystemName = sr.GetString(9),
Position = sr.GetInt32(10),
CopyFieldValueOnCopyItem = sr.GetBool(11),
DeepCopy = sr.GetBool(12),
Guid = sr.GetGuid(13),
SearchPosition = sr.GetInt(14),
SearchWidth = sr.GetInt(15),
IsBase = sr.GetBoolean(16),
UseInGlobalSearch = sr.GetBoolean(19),
PublishedCopyId = sr.GetNullableInt(21),
AllowLocalizedData = sr.GetBoolean("AllowLocalizedData")
};
if (fieldDto.SectionId != sectionId || section == null)
{
section = process.Sections.First(s => s.Id == fieldDto.SectionId);
sectionId = fieldDto.SectionId;
}
fieldDto.FieldTypeInfo = new FieldTypeDto
{
Id = fieldDto.FieldTypeId,
Name = sr.GetString(17),
DataType = sr.GetString(18),
CanBeRequired = sr.GetBoolean(20)
};
section.FieldList.Add(fieldDto);
times.Add((DateTime.Now - start).TotalMilliseconds);
}
Profiler.Profile(() => this.ReadFieldEditors(process, sr));
}
/// <summary>
/// Gets processes Ids that require update.
/// </summary>
/// <returns>The <see cref="IList" />.</returns>
public IList<int> GetProcessesIdsThatRequireUpdate()
{
var result = new List<int>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
const string CommandText = @"
SELECT pp.[ProcessId]
FROM [dbo].[PublishedProcesses] pp
INNER JOIN [dbo].[Processes] p ON pp.[ProcessId] = p.[Id]
WHERE p.[IsRemoved] = 0
AND pp.[IsGlobalSearchUpdateRequired] = 1";
using (var command = new SqlCommand(CommandText, connection))
{
using (var sr = new SafeDataReader(command.ExecuteReader()))
{
while (sr.Read())
{
result.Add(sr.GetInt(0));
}
}
}
}
return result;
}
/// <summary>
/// Gets step list.
/// </summary>
/// <param name="sr">The reader.</param>
/// <param name="stepList">The step list.</param>
private static void GetViewStepList(SafeDataReader sr, List<ViewStepDto> stepList)
{
while (sr.Read())
{
var fieldEditorDto = new ViewStepDto
{
Id = sr.GetInt("Id"),
Class = sr.GetString("StepName"),
SectionId = sr.GetInt32("SectionId")
};
stepList.Add(fieldEditorDto);
}
}
/// <summary>
/// The get process ids info list.
/// </summary>
/// <param name="processIds">The process ids.</param>
/// <returns>The <see cref="IList" />.</returns>
public IList<ProcessGsInfoDto> GetProcessIdsInfoList(IEnumerable<int> processIds)
{
var result = new List<ProcessGsInfoDto>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var enumerableProcessIds = processIds == null ? new int[] { } : (processIds as int[] ?? processIds.ToArray());
if (!enumerableProcessIds.Any())
{
return new List<ProcessGsInfoDto>();
}
const string CommandText = "GetProcessInfoForGlobalSearch";
var connection = ctx.Connection;
using (var cmd = new SqlCommand(CommandText, connection))
{
cmd.CommandType = CommandType.StoredProcedure;
var value = CreateSqlDataRecords(enumerableProcessIds);
cmd.Parameters.AddWithValue("@idValues", value).SqlDbType = SqlDbType.Structured;
using (var sr = new SafeDataReader(cmd.ExecuteReader()))
{
while (sr.Read())
{
result.Add(
new ProcessGsInfoDto
{
FieldName = sr.GetString(0),
Id = sr.GetInt(1),
SystemName = sr.GetString(2),
DisplayName = sr.GetString(3),
IsSystem = sr.GetBoolean(4)
});
}
}
}
}
return result;
}
/// <summary>
/// Retrieves process filters.
/// </summary>
/// <param name="processId">The process id.</param>
/// <returns>The <see cref="IList{T}" />.</returns>
private static IList<ProcessFilterEditDto> FetchProcessFilters(int processId)
{
const string commandText = @"
SELECT [Id]
,[GuidId]
,[LastModifiedOn]
,[Name]
,[FilterDefinition]
FROM [dbo].[Filters]
WHERE [ProcessId] = @processId";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
var list = new List<ProcessFilterEditDto>();
using (var cmd = new SqlCommand(commandText, connection))
{
cmd.Parameters.AddWithValue("@processId", processId);
using (var r = new SafeDataReader(cmd.ExecuteReader()))
{
while (r.Read())
{
var filterInfo = new ProcessFilterEditDto
{
Id = r.GetInt(0),
GuidId = r.GetString(1),
LastModifiedOn = r.GetDateTime(2),
Name = r.GetString(3),
FilterDefinition = r.GetString(4)
};
list.Add(filterInfo);
}
}
return list;
}
}
}
/// <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;
}
private static void FetchComputerConnectionList(SqlConnection connection, ConnectionSettingsDto dto)
{
const string Sql =
@"
SELECT
[Id]
,[GuidId]
,[ConnectionId]
,[ComputerConnection]
,[ConnectionType]
,[PortName]
FROM
[dbo].[MultiplexorComputerConnections]
WHERE [ConnectionId] = @connectionId;
";
using (var cmd = new SqlCommand(Sql, connection))
{
cmd.Parameters.AddWithValue("@connectionId", dto.ConnectionSetupId);
try
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var connectionSettingsDto = new ComputerConnectionEditDto
{
Id = reader.GetInt(0),
Guid = reader.GetGuid(1),
ConnectionId = reader.GetInt32(2),
ComputerConnection = reader.GetString(3),
ConnectionType = !string.IsNullOrEmpty(reader.GetString(4))
? (ConnectionType)Enum.Parse(typeof(ConnectionType), reader.GetString(4))
: ConnectionType.SerialPort,
PortName = reader.GetString(5)
};
dto.ComputerConectionList.Add(connectionSettingsDto);
}
}
}
catch (Exception ex)
{
throw new DBConcurrencyException(ex.Data.ToString());
}
}
}
/// <summary>
/// Fetches removed process list.
/// </summary>
/// <returns>The <see cref="IList" />.</returns>
public IList<ProcessInfoDTO> FetchRemovedProcessList()
{
const string CmdText =
@"
SELECT max(Id), p.[SystemName]
FROM [dbo].[Processes] p
where isRemoved = 1
and IsPublishedCopy = 1
group BY p.[SystemName]";
var result = new List<ProcessInfoDTO>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(CmdText, cn))
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
result.Add(new ProcessInfoDTO { Id = reader.GetInt(0), SystemName = reader.GetString(1) });
}
}
}
return result;
}
/// <summary>
/// Fetches the navigation group edit list.
/// </summary>
/// <param name="currentLocalizationId">The current localization identifier.</param>
/// <returns>IList{NavigationGroupEditDto}.</returns>
public IList<NavigationGroupEditDto> FetchNavigationGroupEditList(int currentLocalizationId)
{
if (currentLocalizationId == 0)
currentLocalizationId = GetLocIdByCultureName(Thread.CurrentThread.CurrentUICulture.Name);
var result = new List<NavigationGroupEditDto>();
const string sql =
@"
SELECT
[Id]
,[Guid]
,ISNULL(l.[Name], g.[Name]) as Name
,[SystemName]
,[Sequence]
,[IconURL]
,[IconId]
FROM
[dbo].[NavigationGroups] g left outer join [dbo].[NavigationGroupLocalizations] l on l.NavigationGroupId = g.Id and l.LocalizationId = @locId
";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (var cmd = new SqlCommand(sql, cn))
{
cmd.Parameters.AddWithValue("@locId", currentLocalizationId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var navigationGroupDto = new NavigationGroupEditDto
{
Id = reader.GetInt(0),
Guid = reader.GetGuid(1),
Name = reader.GetString(2),
SystemName = reader.GetString(3),
Sequence = reader.GetDouble(4),
IconURL = reader.GetString(5),
IconId = reader.GetNullableInt(6)
};
//FetchGroupNavigationItems(cn, navigationGroupDto);
//FetchNavigationGroupSecurityConfigurations(cn, navigationGroupDto);
result.Add(navigationGroupDto);
}
}
foreach (var navigationGroupDto in result)
{
FetchGroupNavigationItems(cn, navigationGroupDto, currentLocalizationId);
FetchNavigationGroupSecurityConfigurations(cn, navigationGroupDto);
}
}
}
return result;
}
/// <summary>
/// Retrieves process views.
/// </summary>
/// <returns>The <see cref="IEnumerable" />.</returns>
public IEnumerable<ProcessViewEditDto> GetProcessViews()
{
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
const string CommandText = @"
SELECT [Id] ,
[ProcessId] ,
[LastModifiedOn] ,
[Guid] ,
[Name] ,
[ViewType]
FROM [dbo].[ProcessViews]
";
var list = new List<ProcessViewEditDto>();
using (var cmd = new SqlCommand(CommandText, connection))
{
using (var r = new SafeDataReader(cmd.ExecuteReader()))
{
while (r.Read())
{
var dto = new ProcessViewEditDto
{
Id = r.GetInt(0),
ProcessId = r.GetInt32(1),
LastModifiedOn = r.GetDateTime(2),
Guid = r.GetGuid(3),
Name = r.GetString(4),
ViewType = r.GetString(5)
};
list.Add(dto);
}
return list;
}
}
}
}
/// <summary>
/// Fetches the group navigation items.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="dto">The dto.</param>
/// <param name="currentLocalizationId">The current localization identifier.</param>
private static void FetchGroupNavigationItems(SqlConnection connection, NavigationGroupEditDto dto, int currentLocalizationId)
{
const string sql =
@"
SELECT
nv.[Id]
,nv.[Guid]
,ISNULL(pl.ProcessName, p.[Name]) + (CASE WHEN ISNULL(pvl.Name, pv.Name) IS NULL THEN '' ELSE ' - ' + ISNULL(pvl.Name, pv.Name) END)
,p.[SystemName]
,nv.[Sequence]
,nv.[PublishedProcessId]
,nv.[IconURL]
,p.[IconId]
,nv.[ProcessViewGuid]
FROM
[dbo].[NavigationItems] nv
INNER JOIN [dbo].[PublishedProcesses] pp ON nv.[PublishedProcessId] = pp.[Id]
INNER JOIN [dbo].[Processes] p ON pp.[ProcessId] = p.[Id]
INNER JOIN Processes p2 ON pp.ProcessGuid = p2.[Guid] and p2.[IsPublishedCopy] = 0
LEFT JOIN [dbo].[ProcessViews] pv ON pv.[Guid] = nv.ProcessViewGuid AND pv.ProcessId = p.Id
LEFT OUTER JOIN dbo.ProcessLocalizations pl ON p2.Id = pl.ProcessId AND pl.LocalizationId = @locId
LEFT OUTER JOIN dbo.ProcessViewLocalizations pvl ON p2.Id = pl.ProcessId AND pvl.LocalizationId = @locId
WHERE
p.[IsRemoved] = 0 AND
p.IsInactive = 0 AND
nv.[NavigationGroupId] = @groupId
UNION
SELECT
nv.[Id]
,nv.[Guid]
,nv.[Name]
,NULL
,nv.[Sequence]
,NULL
,nv.[IconURL]
,NULL
,NULL
FROM
[dbo].[NavigationItems] nv
WHERE nv.[SystemName] IS NULL AND
nv.[PublishedProcessId] IS NULL AND
nv.[NavigationGroupId] = @groupId;
";
using (var cmd = new SqlCommand(sql, connection))
{
cmd.Parameters.AddWithValue("@groupId", dto.Id);
cmd.Parameters.AddWithValue("@locId", currentLocalizationId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var navigationItemDto = new NavigationItemEditDto
{
Id = reader.GetInt(0),
Guid = reader.GetGuid(1),
Name = reader.GetString(2),
Sequence = reader.GetDouble(4),
PublishedProcessId = reader.GetNullableInt(5),
IconURL = reader.GetString(6),
IconId = reader.GetNullableInt(7),
ProcessViewGuid = reader.GetNullableGuid(8)
};
//FetchNavigationItemSecurityConfigurations(connection, navigationItemDto);
dto.NavigationItems.Add(navigationItemDto);
}
}
foreach (var navigationItemDto in dto.NavigationItems)
{
FetchNavigationItemSecurityConfigurations(connection, navigationItemDto);
}
}
}
/// <summary>
/// Retrieves cross ref fields.
/// </summary>
/// <param name="processId">The process id.</param>
/// <param name="crossRefProcessGuid">The cross ref process GUID.</param>
/// <param name="includeInheritedFields">The include inherited fields.</param>
/// <returns>The <see cref="IList" />.</returns>
public IList<CrossRefFieldDto> FetchCrossRefFields(int processId, Guid crossRefProcessGuid = new Guid(), bool includeInheritedFields = false)
{
var result = new List<CrossRefFieldDto>();
string cmdText;
if (crossRefProcessGuid == Guid.Empty)
{
if (includeInheritedFields)
{
cmdText = @"
WITH ProcessList(
Id,
PublishedProcessId,
IsBase
)
AS
(
SELECT p.Id AS Id,
pp.Id AS PublishedProcessId,
0 AS IsBase
FROM Processes p
LEFT OUTER JOIN PublishedProcesses pp ON pp.Id = p.BaseProcessId
WHERE p.Id = @processId
UNION ALL
SELECT pp.ProcessId,
pp.Id AS PublishedProcessId,
1 AS IsBase
FROM Processes p
INNER JOIN PublishedProcesses pp
ON p.BaseProcessId = pp.Id
INNER JOIN processes p1
ON pp.ProcessId = p1.Id
INNER JOIN ProcessList pl
ON p.id = pl.Id
)
SELECT f.[Name]
, f.[SystemName]
, f.[Id]
, x.[AllowMultiple]
, x.[DisplayFieldName]
, p.[SystemName]
, p.[Id]
FROM [dbo].[Sections] s
INNER JOIN ProcessList pl ON s.ProcessId = pl.Id
INNER JOIN [dbo].[Fields] f ON s.[Id] = f.[SectionId]
INNER JOIN [dbo].[CrossRefRequredFieldStep] x ON f.[Id] = x.[FieldId]
INNER JOIN [dbo].[PublishedProcesses] [pp] ON [pp].[Id] = x.CrossRefProcessId
INNER JOIN [dbo].[Processes] [p] ON [p].[Id] = pp.ProcessId";
}
else
{
cmdText = @"
SELECT f.[Name]
, f.[SystemName]
, f.[Id]
, x.[AllowMultiple]
, x.[DisplayFieldName]
, p.[SystemName]
, p.[Id]
FROM [dbo].[Sections] s
INNER JOIN [dbo].[Fields] f ON s.[Id] = f.[SectionId]
INNER JOIN [dbo].[CrossRefRequredFieldStep] x ON f.[Id] = x.[FieldId]
INNER JOIN [dbo].[PublishedProcesses] [pp] ON [pp].[Id] = x.CrossRefProcessId
INNER JOIN [dbo].[Processes] [p] ON [p].[Id] = pp.ProcessId
WHERE s.[ProcessId] = @processId";
}
}
else
{
cmdText = @"
DECLARE @pp_id INT
SELECT @pp_id = pp.[Id] FROM [dbo].[PublishedProcesses] pp WHERE pp.[ProcessGuid] = @crossRefProcessGuid
SELECT f.[Name]
, f.[SystemName]
, f.[Id]
, x.[AllowMultiple]
, x.[DisplayFieldName]
, p.[SystemName]
, p.[Id]
FROM [dbo].[Sections] s
INNER JOIN [dbo].[Fields] f ON s.[Id] = f.[SectionId]
INNER JOIN [dbo].[CrossRefRequredFieldStep] x ON f.[Id] = x.[FieldId]
INNER JOIN [dbo].[PublishedProcesses] [pp] ON [pp].[Id] = x.CrossRefProcessId
INNER JOIN [dbo].[Processes] [p] ON [p].[Id] = pp.ProcessId
WHERE s.[ProcessId] = @processId AND x.[CrossRefProcessId] = @pp_id
DECLARE @pp_systemName NVARCHAR(200)
SELECT @pp_systemName = p.[SystemName] FROM [dbo].[Processes] p WHERE p.[Guid] = @crossRefProcessGuid
SELECT f.[Name]
, f.[SystemName]
, f.[Id]
, p.[SystemName]
, p.[Id]
FROM [dbo].[Sections] s
INNER JOIN [dbo].[Fields] f ON s.[Id] = f.[SectionId]
INNER JOIN [dbo].[stepChecklist] x ON f.[Id] = x.[FieldId]
INNER JOIN [dbo].[Processes] p ON p.[IsRemoved] = 0 AND p.[IsPublishedCopy] = 1 AND p.[SystemName] = x.[AnswerProcessSystemName]
WHERE s.[ProcessId] = @processId AND x.[AnswerProcessSystemName] = @pp_SystemName";
}
cmdText += @"
";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(cmdText, cn))
{
cmd.Parameters.AddWithValue("@processId", processId);
cmd.Parameters.AddWithValue("@crossRefProcessGuid", crossRefProcessGuid);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
result.Add(new CrossRefFieldDto
{
Name = reader.GetString(0),
SystemName = reader.GetString(1),
FieldId = reader.GetInt(2),
AllowMany = reader.GetBoolean(3),
DisplayField = reader.GetString(4),
ProcessSystemName = reader.GetString(5),
ProcessId = reader.GetInt(6),
ColumnType = reader.GetBoolean(3) ? ColumnTypes.MultiReference : ColumnTypes.Reference
});
}
if (reader.NextResult())
{
while (reader.Read())
{
result.Add(new CrossRefFieldDto
{
Name = reader.GetString(0),
SystemName = reader.GetString(1),
FieldId = reader.GetInt32(2),
ProcessSystemName = reader.GetString(3),
ProcessId = reader.GetInt32(4),
ColumnType = ColumnTypes.Checklist
});
}
}
}
}
}
return result;
}
/// <summary>
/// Fetches the navigation group security configurations.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="dto">The dto.</param>
private static void FetchNavigationGroupSecurityConfigurations(SqlConnection connection, NavigationGroupEditDto dto)
{
const string Sql =
@"
SELECT
[Id]
,[RoleId]
,[CanView]
FROM
[dbo].[NavigationGroupSecurityConfigurations]
WHERE [NavigationGroupId] = @groupId;
";
using (var cmd = new SqlCommand(Sql, connection))
{
cmd.Parameters.AddWithValue("@groupId", dto.Id);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var securityConfigurationDto = new NavigationGroupSecurityConfigurationDto
{
Id = reader.GetInt(0),
RoleId = reader.GetInt(1),
CanView = reader.GetBoolean(2)
};
dto.SecurityConfigurations.Add(securityConfigurationDto);
}
}
}
}
/// <summary>
/// Retrieves reverse cross ref options step.
/// </summary>
/// <param name="processName">The process name.</param>
/// <param name="fieldName">The field name.</param>
/// <param name="isPublishedCopy">The is published copy.</param>
/// <returns>The <see cref="ReverseCrossRefOptionsStepDto" />.</returns>
public ReverseCrossRefOptionsStepDto FetchReverseCrossRefOptionsStep(string processName, string fieldName, bool isPublishedCopy = false)
{
const string CmdText =
@"
DECLARE @fieldId AS INT
SELECT @fieldId = f.Id
FROM
[dbo].[Processes] p
INNER JOIN [dbo].[Sections] s ON s.ProcessId = p.Id
INNER JOIN [dbo].[Fields] f ON f.SectionId = s.Id
WHERE p.[SystemName] = @processName AND p.IsRemoved = 0 AND p.IsPublishedCopy = @isPublishedCopy AND f.SystemName = @fieldName;
EXEC [dbo].[GetReverseCrossRefOptionsStep] @FieldId = @fieldId;
";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
using (var cmd = new SqlCommand(CmdText, ctx.Connection))
{
cmd.Parameters.AddWithValue("@processName", processName);
cmd.Parameters.AddWithValue("@fieldName", fieldName);
cmd.Parameters.AddWithValue("@isPublishedCopy", isPublishedCopy);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
var dto = new ReverseCrossRefOptionsStepDto
{
Id = reader.GetInt(0),
FieldId = reader.GetInt(1),
AllowAddNew = reader.GetBoolean(2),
AllowLink = reader.GetBoolean(3),
ShowLatestVersion = reader.GetBoolean(4)
};
return dto;
}
}
}
return null;
}
/// <summary>
/// Gets all processes ids.
/// </summary>
/// <returns>The <see cref="IList" />.</returns>
public IList<int> GetAllProcessesIds()
{
var result = new List<int>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
const string CommandText = @"
SELECT p.[Id]
FROM [dbo].[Processes] p
WHERE p.[IsRemoved] = 0
AND p.[IsPublishedCopy] = 1";
using (var command = new SqlCommand(CommandText, connection))
{
using (var sr = new SafeDataReader(command.ExecuteReader()))
{
while (sr.Read())
{
result.Add(sr.GetInt(0));
}
}
}
}
return result;
}
private static void FetchEDCConnectionList(SqlConnection connection, EDCEditDto dto)
{
const string Sql =
@"
SELECT
[Id]
,[GuidId]
,[EDCId]
,[ConnectionSetupId]
,[FirstExpression]
FROM
[dbo].[EDCConnections]
WHERE [EDCId] = @edcId;
";
using (var cmd = new SqlCommand(Sql, connection))
{
cmd.Parameters.AddWithValue("@edcId", dto.Id);
var result = new List<EDCConnectionEditDto>();
try
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var param = new EDCConnectionEditDto
{
Id = reader.GetInt(0),
Guid = reader.GetGuid(1),
EDCId = reader.GetInt32(2),
ConnectionSetupId = reader.GetInt32(3),
FirstExpression = reader.GetString(4)
};
result.Add(param);
}
foreach (var editDto in result)
{
editDto.Connection = new ConnectionSettingsDto();
FetchConnectionSetting(connection, editDto);
}
dto.ConnectionList.AddRange(result);
}
}
catch (Exception ex)
{
throw new ArgumentException(ex.Data.ToString());
}
}
}
/// <summary>
/// Fetches the system options.
/// </summary>
/// <returns>SystemOptionsDTO.</returns>
public SystemOptionsDTO FetchSystemOptions()
{
const string Sql = @"
SELECT TOP 1 [SystemOptionsID]
,[LastModifiedOn]
,[LoginHTML]
,[AuditLogin]
,[AuditLogout]
,[AnimateWindows]
,[TempDocumentUNC]
,[TempDocumentURI]
,[PaperclipUNC]
,[PaperclipURI]
,[FileUploadURI]
,[FileProcessorURI]
,[InactivitySetting]
,[InactivityMinutes]
,[DaysBeforeAccountDisabled]
,[DelayAfterInvalidLogin]
,[DelayAfterInvalidLoginTimeout]
,[InvalidLoginAttemptCount]
,[InvalidLoginAttemptTime]
,[DisableAccountSetting]
,[InvalidLoginTimeout]
,[SessionInactivityTimeoutAction]
,[IsMandatoryPasswordChange]
,[PasswordExpirationAction]
,[PasswordExpirationDays]
,[MinimumPasswordLength]
,[EnableSpecialChar]
,[EnableUpperLowerCase]
,[PasswordHistoryCount]
,[PasswordStrengthExpression]
,[NotifyLastSucccessfulLogin]
,[NotifyLastUnsucccessfulLogin]
,[NotifyUserLastLoginAttempts]
,[NotifyLastLoginAttemptsDays]
,[NotifyUserSecurityChanges]
,[SMTPServer]
,[SMTPPort]
,[SMTPUseSsl]
,[SMTPUseAuth]
,[SMTPAuthName]
,[SMTPAuthPass]
,[EmailFromAddress]
,[EmailFromCurrentUser]
,[ReportsPath]
,[IsUnderMaintenance]
,[Theme]
,[AllowRememberMe]
,[MaxFileSize]
,[ShowWarningMessage]
,[WarningMessage]
,[WarningCaption]
,[ShowInfoMessage]
,[InfoMessage]
,[InfoCaption]
,[ShowSuccessMessage]
,[SuccessMessage]
,[SuccessCaption]
,[RestrictExportTo]
,[DisableSearchGrouping]
,[DisableSearchAll]
FROM [dbo].[SystemOptions]
SELECT TOP 1
sso.[IsEnabled] AS SSOEnabled
,sso.[AllowSSOApproval]
,sso.[SAMLVersion]
,sso.[Issuer] AS SSOIssuer
,sso.[IdPTargetUrl]
,sso.[IdPLogoutUrl]
,CASE LEN(sso.IDPCertificate) WHEN 0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS HasNoSSOCertificate
FROM [dbo].[SSOOptions] sso
SELECT
ldap.[Id]
,ldap.[Name]
,ldap.[ServerType]
,ldap.[UseForLogin]
,ldap.[ServerPath]
,ldap.[Username]
,ldap.[Password]
,ldap.[RootPath]
,ldap.[SearchFilter]
,ldap.[UseSSL]
FROM [dbo].[LdapProfiles] ldap
";
/*
SELECT id ,
Body ,
Header ,
Date
FROM dbo.news
ORDER BY Date DESC
*/
SystemOptionsDTO dto = null;
Database.GetDataReader(
Sql,
reader =>
{
if (reader == null)
throw new DataAccessException(Resources.FailedToRetrieveSystemOptions);
if (!reader.Read())
{
dto = new SystemOptionsDTO { InactivityMinutes = 1, MinimumPasswordLength = 1};
InsertSystemOptions(dto);
}
else
{
using (var sr = new SafeDataReader(reader))
{
dto = new SystemOptionsDTO
{
SystemOptionsId = sr.GetInt("SystemOptionsID"),
LoginHtml = sr.GetString("LoginHTML"),
AuditLogin = sr.GetBool("AuditLogin"),
AuditLogout = sr.GetBool("AuditLogout"),
AnimateWindows = sr.GetBool("AnimateWindows", true),
TempDocumentUNC = sr.GetString("TempDocumentUNC"),
TempDocumentURI = sr.GetString("TempDocumentURI"),
PaperclipUNC = sr.GetString("PaperclipUNC"),
PaperclipURI = sr.GetString("PaperclipURI"),
FileUploadURI = sr.GetString("FileUploadURI"),
FileProcessorURI = sr.GetString("FileProcessorURI"),
InactivitySetting = sr.GetEnum("InactivitySetting", InactivityTypesEnum.None),
InactivityMinutes = sr.GetInt("InactivityMinutes"),
DaysBeforeAccountDisabled = sr.GetInt("DaysBeforeAccountDisabled"),
DelayAfterInvalidLogin = sr.GetBool("DelayAfterInvalidLogin"),
DelayAfterInvalidLoginTimeout = sr.GetInt("DelayAfterInvalidLoginTimeout"),
InvalidLoginAttemptCount = sr.GetInt("InvalidLoginAttemptCount"),
InvalidLoginAttemptTime = sr.GetInt("InvalidLoginAttemptTime"),
DisableAccountSetting = sr.GetEnum("DisableAccountSetting", DisableAccountActionsType.DisableAdmin),
InvalidLoginTimeout = sr.GetInt("InvalidLoginTimeout"),
SessionInactivityTimeoutAction = sr.GetEnum("SessionInactivityTimeoutAction", InactivityTimoutActionEnum.None),
IsMandatoryPasswordChange = sr.GetBool("IsMandatoryPasswordChange"),
PasswordExpirationAction = sr.GetEnum("PasswordExpirationAction", PasswordExpirationActions.NoAction),
PasswordExpirationDays = sr.GetInt("PasswordExpirationDays"),
MinimumPasswordLength = sr.GetInt("MinimumPasswordLength"),
EnableSpecialChar = sr.GetBool("EnableSpecialChar"),
EnableUpperLowerCase = sr.GetBool("EnableUpperLowerCase"),
PasswordHistoryCount = sr.GetInt("PasswordHistoryCount"),
PasswordStrengthExpression = sr.GetString("PasswordStrengthExpression"),
NotifyUserLastSucccessfulLogin = sr.GetBool("NotifyLastSucccessfulLogin"),
NotifyUserLastUnsucccessfulLogin = sr.GetBool("NotifyLastUnsucccessfulLogin"),
NotifyUserLastLoginAttempts = sr.GetBool("NotifyUserLastLoginAttempts"),
NotifyUserLastLoginAttemptsDays = sr.GetInt("NotifyLastLoginAttemptsDays"),
NotifyUserSecurityChanges = sr.GetBool("NotifyUserSecurityChanges"),
SMTPServer = sr.GetString("SMTPServer"),
SMTPPort = sr.GetInt("SMTPPort", 25),
SMTPUseSsl = sr.GetBool("SMTPUseSsl"),
SMTPUseAuth = sr.GetBool("SMTPUseAuth"),
SMTPAuthName = sr.GetString("SMTPAuthName"),
SMTPAuthPass = sr.GetString("SMTPAuthPass"),
EmailFromAddress = sr.GetString("EmailFromAddress"),
EmailFromCurrentUser = sr.GetBoolean("EmailFromCurrentUser"),
ReportsPath = sr.GetString("ReportsPath"),
IsUnderMaintenance = sr.GetBool("IsUnderMaintenance"),
Theme = sr.GetString("Theme"),
AllowRememberMe = sr.GetBool("AllowRememberMe"),
MaxFileSize = (ulong)sr.GetInt64("MaxFileSize"),
LdapProfiles = new List<LdapProfileDTO>(),
SSOOptions = new SSOOptionsDTO(),
ShowWarning = sr.GetBool("ShowWarningMessage"),
WarningMessage = sr.GetString("WarningMessage"),
WarningCaption = sr.GetString("WarningCaption"),
ShowInfo = sr.GetBool("ShowInfoMessage"),
InfoMessage = sr.GetString("InfoMessage"),
InfoCaption = sr.GetString("InfoCaption"),
ShowSuccess = sr.GetBool("ShowSuccessMessage"),
SuccessMessage = sr.GetString("SuccessMessage"),
SuccessCaption = sr.GetString("SuccessCaption"),
RestrictExportTo = sr.GetInt("RestrictExportTo"),
DisableSearchGrouping = sr.GetBool("DisableSearchGrouping"),
DisableSearchAll = sr.GetBool("DisableSearchAll"),
};
sr.NextResult();
while (sr.Read())
{
dto.SSOOptions.IsEnabled = sr.GetBool("SSOEnabled");
dto.SSOOptions.AllowSSOApproval = sr.GetBool("AllowSSOApproval");
dto.SSOOptions.SAMLVersion = sr.GetString("SAMLVersion");
dto.SSOOptions.Issuer = sr.GetString("SSOIssuer");
dto.SSOOptions.IdPTargetUrl = sr.GetString("IdPTargetUrl");
dto.SSOOptions.IdPLogoutUrl = sr.GetString("IdPLogoutUrl");
dto.SSOOptions.HasNoCertificate = sr.GetBoolean("HasNoSSOCertificate");
}
sr.NextResult();
while (sr.Read())
{
dto.LdapProfiles.Add(
new LdapProfileDTO
{
Id = sr.GetInt32("Id"),
Name = sr.GetString("Name"),
ServerType = (LdapServerTypes) sr.GetInt32("ServerType"),
UseForLogin = sr.GetBool("UseForLogin"),
ServerPath = sr.GetString("ServerPath"),
Username = TryDecrypt(sr.GetString("Username")),
Password = TryDecrypt(sr.GetString("Password")),
RootPath = sr.GetString("RootPath"),
SearchFilter = sr.GetString("SearchFilter"),
UseSSL = sr.GetBoolean("UseSSL")
});
}
}
}
//reader.NextResult();
//while (reader.Read())
//{
// dto.NewsList.Add(new NewsDto
// {
// Id = reader.GetInt32(0),
// Body = reader.GetString(1),
// Header = reader.GetString(2),
// Date = reader.GetDateTime(3)
// });
//}
});
return dto;
}
