/// <summary>
/// Reads required rule config fields.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
/// ProcessCommandSecurityConfigurations
private static void ReadRequiredRuleConfigFields(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
int? requiredConfigId = null;
RequiredRuleConfigEditDto config = null;
while (sr.Read())
{
var configFieldDto = new RequiredRuleConfigFieldEditDto
{
Id = sr.GetInt32(0),
RequiredConfigId = sr.GetInt32(1),
Guid = sr.GetGuid(2),
FieldGuid = sr.GetGuid(3),
IsRequired = sr.GetBoolean(4)
};
if (configFieldDto.RequiredConfigId != requiredConfigId)
{
config = process.RequiredRuleConfigs.First(rrc => rrc.Id == configFieldDto.RequiredConfigId);
requiredConfigId = configFieldDto.RequiredConfigId;
}
config.FieldList.Add(configFieldDto);
}
}
public IEnumerable<EDCEditDto> FetchEdcList()
{
const string sql = @"
SELECT [Id]
,[Name]
,[SystemName]
,[Documentation]
,[GuidId]
FROM [dbo].[ExternalDataConfiguration] edc
";
var result = new List<EDCEditDto>();
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))
{
using (var sr = new SafeDataReader(cmd.ExecuteReader()))
{
while (sr.Read())
{
var parameter = new EDCEditDto
{
Id = sr.GetInt32(0),
Name = sr.GetString(1),
SystemName = sr.GetString(2),
Documentation = sr.GetString(3),
Guid = sr.GetGuid(4)
};
result.Add(parameter);
}
}
foreach (var dto in result)
{
dto.ConnectionList = new List<EDCConnectionEditDto>();
dto.DataVariableList = new List<EDCDataVariablesEditDto>();
FetchEDCConnectionList(cn, dto);
FetchDataVariableList(cn, dto);
}
}
}
return result;
}
/// <summary>
/// Fetch System Parameters
/// </summary>
/// <returns>IEnumerable{SystemParametersDto}.</returns>
/// <exception cref="DataAccessException"></exception>
public IEnumerable<SystemParametersDto> FetchSystemParameters()
{
const string Sql = @"
SELECT [Id]
,[Name]
,[Documentation]
,[Type]
,[ProcessSystemName]
,[Expression]
,[GuidId]
,[LastModifiedOn]
FROM [dbo].[SystemParameters] sp
WHERE sp.IsRemoved = 0
";
var result = new List<SystemParametersDto>();
Database.GetDataReader(
Sql,
reader =>
{
if (reader == null)
{
throw new DataAccessException(Resources.FailedToRetrieveSystemOptions);
}
using (var sr = new SafeDataReader(reader))
{
while (reader.Read())
{
var parameter = new SystemParametersDto
{
ParameterId = sr.GetInt32(0),
Name = sr.GetString(1),
Documentation = sr.GetString(2),
ParameterType = !string.IsNullOrEmpty(reader.GetString(3))
? (SystemParameterType)Enum.Parse(typeof(SystemParameterType), reader.GetString(3))
: SystemParameterType.Int,
PersonProcessSystemName = sr.GetString(4),
Expression = sr.GetString(5),
Guid = sr.GetGuid(6),
LastModifiedOn = sr.GetDateTime(7)
};
result.Add(parameter);
}
}
});
return result;
}
/// <summary>
/// Reads search display fields.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private static void ReadSearchDisplayFields(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
while (sr.Read())
{
process.Search.Add(
new ProcessSearchEditDto
{
Id = sr.GetInt32(0),
ProcessId = sr.GetInt32(1),
GuidId = sr.GetGuid(2),
FieldSystemName = sr.GetString(3),
Position = sr.GetInt32(4),
Width = sr.GetInt32(5),
ShowInList = sr.GetBoolean(6)
});
}
}
/// <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 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>
/// Retrieves dashboard gadgets for a given Dashboard page and fill up given DTO object collection.
/// </summary>
/// <param name="page">Dashboard page DTO Object.</param>
private static void ReadDashboardGadgets(DashboardPageDto page)
{
const string sql = @"
SELECT [Id]
,[Guid]
,[Name]
,[GadgetType]
,[Settings]
,[IsLocked]
FROM [DashboardGadgets]
WHERE [DashboardPageId] = @pageId";
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("@pageId", page.Id);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var gadgetDto = new DashboardGadgetDto
{
Id = reader.GetInt32(0),
Guid = reader.GetGuid(1),
Name = reader.GetString(2),
GadgetType = reader.GetString(3),
Settings = reader.GetString(4),
IsLocked = reader.GetSafeBool(5)
};
page.Gadgets.Add(gadgetDto);
}
}
}
}
}
/// <summary>
/// Retrieves eSync pending republishes.
/// </summary>
/// <returns>The <see cref="IList" />.</returns>
public IList<ESyncPendingRepublishDto> FetchESyncPendingRepublishes()
{
const string CommandText =
@"
SELECT
[Id]
,[ProcessGuid]
,[ProcessSystemName]
FROM
[dbo].[ESyncPendingRepublishes];";
using (var cmd = new SqlCommand(CommandText))
{
var result = new List<ESyncPendingRepublishDto>();
Database.GetDataReader(
cmd,
dr =>
{
using (var reader = new SafeDataReader(dr))
{
while (reader.Read())
{
var dto = new ESyncPendingRepublishDto
{
Id = reader.GetInt32(0),
ProcessGuid = reader.GetGuid(1),
ProcessSystemName = reader.GetString(2)
};
result.Add(dto);
}
}
});
return result;
}
}
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>
/// 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 checklist 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="ChecklistStepDto" />.</returns>
public ChecklistStepDto FetchChecklistStep(string processName, string fieldName, bool isPublishedCopy = false)
{
const string CommandText = @"
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].[GetChecklistStep] @FieldId = @fieldId;";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
using (var cmd = new SqlCommand(CommandText, 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 ChecklistStepDto
{
QuestionProcessSystemName = reader.GetString(0),
AnswerProcessSystemName = reader.GetString(1),
AnswerProcessDefaultStateGuid = reader.GetGuid(2),
CommentsFieldSystemName = reader.GetString(3),
IsSingleQuestionView = reader.GetBool(4),
ListDisplayFieldSystemName = reader.GetString(5),
AllowAdhocQuestions = reader.GetBool(6),
FilterDefinition = reader.GetString(7),
FilterGuid = reader.IsDBNull(8) ? null : (Guid?)reader.GetGuid(8),
CanChangeItemState = reader.GetBool(9),
ShowLinkedItems = reader.GetBool(10),
SortFieldSystemName = reader.GetString(11),
SortDirection = reader.GetEnum(12, SortDirection.Ascending),
HideValidationIcon = reader.GetBool(13)
};
return dto;
}
}
}
return null;
}
/// <summary>
/// Populates the result for SQL oracle.
/// </summary>
/// <param name="reader">The reader.</param>
/// <returns>List{FieldInfoDto}.</returns>
private List<FieldInfoDto> PopulateResultForSqlOracle(SafeDataReader reader)
{
var result = new List<FieldInfoDto>();
while (reader.Read())
{
result.Add(new FieldInfoDto
{
Name = reader.GetString(1),
SystemName = "\"" + reader.GetString(2) + "\"",
FieldTypeId = reader.GetInt32(3),
ProcessID = reader.GetInt32(4),
ProcessName = reader.GetString(5),
ProcessSystemName = "\"" + reader.GetString(6) + "\"",
DefinedIn = reader.GetString(8),
DataType = reader.GetString(9),
Guid = reader.GetGuid(10),
IsRichTextField = reader.GetBoolean(11),
});
}
if (reader.NextResult() && reader.Read())
{
//var baseProcessId = reader.GetInt32(0);
result.AddRange(FetchFieldList());
}
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;
}
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());
}
}
}
public IEnumerable<ConnectionSettingsDto> FetchConnectionSetupList()
{
const string sql = @"
SELECT [ConnectionSetupId]
,[Name]
,[SystemName]
,[Documentation]
,[ConnectionType]
,[PortName]
,[BaudRate]
,[Parity]
,[Dtr]
,[FlowControlHighSignal]
,[ParityErrorChecking]
,[FlowControl]
,[Rts]
,[DataBits]
,[StopBits]
,[GuidId]
,[Vid]
,[Pid]
,[ParentIdPrefix]
FROM [dbo].[MultiplexorConnections] cs
WHERE cs.IsRemoved = 0
";
var result = new List<ConnectionSettingsDto>();
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))
{
using (var sr = new SafeDataReader(cmd.ExecuteReader()))
{
while (sr.Read())
{
var parameter = new ConnectionSettingsDto
{
ConnectionSetupId = sr.GetInt32(0),
Name = sr.GetString(1),
SystemName = sr.GetString(2),
Documentation = sr.GetString(3),
ConnectionType = !string.IsNullOrEmpty(sr.GetString(4))
? (ConnectionType)Enum.Parse(typeof(ConnectionType), sr.GetString(4))
: ConnectionType.SerialPort,
PortName = sr.GetString(5),
BaudRate = sr.GetString(6),
Parity = !string.IsNullOrEmpty(sr.GetString(7))
? (SerialPortParity)Enum.Parse(typeof(SerialPortParity), sr.GetString(7))
: SerialPortParity.None,
Dtr = sr.GetBoolean(8),
FlowControlHighSignal = sr.GetInt32(9),
ParityErrorChecking = sr.GetBoolean(10),
FlowControl = !string.IsNullOrEmpty(sr.GetString(11))
? (FlowControl)Enum.Parse(typeof(FlowControl), sr.GetString(11))
: FlowControl.None,
Rts = sr.GetBoolean(12),
DataBits = sr.GetInt32(13),
StopBits = !string.IsNullOrEmpty(sr.GetString(14))
? (StopBits)Enum.Parse(typeof(StopBits), sr.GetString(14))
: StopBits.One,
Guid = sr.GetGuid(15),
Vid = sr.GetString(16),
Pid = sr.GetString(17),
ParentIdPrefix = sr.GetString(18)
};
result.Add(parameter);
}
}
foreach (var dto in result)
{
dto.ComputerConectionList = new List<ComputerConnectionEditDto>();
FetchComputerConnectionList(cn, dto);
}
}
}
return result;
}
public static ConnectionSettingsDto FetchConnectionSetup(SqlConnection connection, int Id)
{
const string sql = @"
SELECT [ConnectionSetupId]
,[Name]
,[SystemName]
,[Documentation]
,[ConnectionType]
,[PortName]
,[BaudRate]
,[Parity]
,[Dtr]
,[FlowControlHighSignal]
,[ParityErrorChecking]
,[FlowControl]
,[Rts]
,[DataBits]
,[StopBits]
,[GuidId]
,[Vid]
,[Pid]
,[ParentIdPrefix]
FROM [dbo].[MultiplexorConnections] mpxc
WHERE mpxc.IsRemoved = 0
AND mpxc.[ConnectionSetupId] = @id
";
var result = new ConnectionSettingsDto();
using (var cmd = new SqlCommand(sql, connection))
{
cmd.Parameters.AddWithValue("@id", Id);
using (var sr = new SafeDataReader(cmd.ExecuteReader()))
{
while (sr.Read())
{
var parameter = new ConnectionSettingsDto
{
ConnectionSetupId = sr.GetInt32(0),
Name = sr.GetString(1),
SystemName = sr.GetString(2),
Documentation = sr.GetString(3),
ConnectionType = !string.IsNullOrEmpty(sr.GetString(4))
? (ConnectionType)Enum.Parse(typeof(ConnectionType), sr.GetString(4))
: ConnectionType.SerialPort,
PortName = sr.GetString(5),
BaudRate = sr.GetString(6),
Parity = !string.IsNullOrEmpty(sr.GetString(7))
? (SerialPortParity)Enum.Parse(typeof(SerialPortParity), sr.GetString(7))
: SerialPortParity.None,
Dtr = sr.GetBoolean(8),
FlowControlHighSignal = sr.GetInt32(9),
ParityErrorChecking = sr.GetBoolean(10),
FlowControl = !string.IsNullOrEmpty(sr.GetString(11))
? (FlowControl)Enum.Parse(typeof(FlowControl), sr.GetString(11))
: FlowControl.None,
Rts = sr.GetBoolean(12),
DataBits = sr.GetInt32(13),
StopBits = !string.IsNullOrEmpty(sr.GetString(14))
? (StopBits)Enum.Parse(typeof(StopBits), sr.GetString(14))
: StopBits.One,
Guid = sr.GetGuid(15),
Vid = sr.GetString(16),
Pid = sr.GetString(17),
ParentIdPrefix = sr.GetString(18)
};
result = parameter;
}
}
result.ComputerConectionList = new List<ComputerConnectionEditDto>();
FetchComputerConnectionList(connection, result);
}
return result;
}
/// <summary>
/// Retrieves single cross references to process.
/// </summary>
/// <param name="definingProcessPublishedId">The defining process published id.</param>
/// <param name="referencedProcessSystemName">The referenced process system name.</param>
/// <returns>The <see cref="IList" />.</returns>
public IEnumerable<FieldInfoDto> FetchSingleCrossReferencesToProcess(int definingProcessPublishedId, string referencedProcessSystemName)
{
const string CommandText = @"
SELECT
f.[Id]
,f.[Guid]
,f.[Name]
,f.[SystemName]
,f.[FieldTypeId]
,p.[SystemName] AS 'DefinedIn'
FROM
[dbo].[Fields] f
INNER JOIN [dbo].[Sections] s ON s.[Id] = f.[SectionId]
INNER JOIN [dbo].[Processes] p ON p.[Id] = s.[ProcessId]
INNER JOIN [dbo].[PublishedProcesses] pp ON pp.[ProcessId] = p.[Id]
INNER JOIN [dbo].[CrossRefRequredFieldStep] crrs ON crrs.[FieldId] = f.[Id]
INNER JOIN [dbo].[PublishedProcesses] pp2 ON pp2.[Id] = crrs.[CrossRefProcessId]
INNER JOIN [dbo].[Processes] p2 ON p2.[Id] = pp2.[ProcessId]
WHERE f.[IsRemoved] = 0 AND pp.[Id] = @definingProcessPublishedId AND p2.[SystemName] = @referencedProcessSystemName AND crrs.[AllowMultiple] = 0";
var fields = new List<FieldInfoDto>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
using (var cmd = new SqlCommand(CommandText, ctx.Connection))
{
cmd.Parameters.AddWithValue("@definingProcessPublishedId", definingProcessPublishedId);
cmd.Parameters.AddWithValue("@referencedProcessSystemName", referencedProcessSystemName);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var dto = new FieldInfoDto
{
Id = reader.GetInt32(0),
Guid = reader.GetGuid(1),
Name = reader.GetString(2),
SystemName = reader.GetString(3),
DataType = ColumnTypes.Reference.ToString(),
FieldTypeId = reader.GetInt32(4),
DefinedIn = reader.GetString(5)
};
fields.Add(dto);
}
}
}
}
return fields;
}
public IPagedList<ProcessInfoDTO> FetchList(string filter, int pageNumber, int pageSize, string filterExpression)
{
var result = new PagedList<ProcessInfoDTO>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
var sql = @"
DECLARE @p0 AS INT
SET @p0 = {1}
DECLARE @p1 AS INT
SET @p1 = {2};
SELECT [t1].[Id],
[t1].[Name],
[t1].[Description],
[t1].[Icon],
CASE
WHEN [t1].PublishedCopyId IS NULL THEN 0
ELSE 1
END AS IsPublished,
[t1].[SystemName],
[t1].[Guid],
[t1].[Color],
[t1].IsInactive,
[t1].IsSystem,
[t1].[Documentation]
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY [t0].[Name], [t0].[Id]) AS
[ROW_NUMBER],
[t0].[Name],
[t0].SystemName,
[t0].[Guid],
[t0].Id,
[t0].[Description],
[t0].[Documentation],
[t0].IsInactive,
[t0].IsSystem,
[i].Icon,
[c].Color,
(
SELECT id
FROM Processes p
WHERE p.Guid = [t0].Guid
AND p.IsPublishedCopy = 1
AND p.IsRemoved = 0
) AS PublishedCopyId
FROM [dbo].[Processes] AS [t0]
LEFT OUTER JOIN Icons i
ON [t0].IconId = [i].Id
LEFT OUTER JOIN Colors c
ON [t0].ColorId = [c].Id
{0}
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY
[t1].[ROW_NUMBER]
";
var fieldFilterWhere = AdoHelper.BuildFilterStatement(FilterDescriptor.GetFilterList(filterExpression), "Processes", tableAlias: "[t0]");
var globalFilterWhere = AdoHelper.BuildFilterStatement(filter, new[] { "[t0].Name", "[t0].Description" });
var where = string.Format(CultureInfo.InvariantCulture, "WHERE IsRemoved = 0 AND IsPublishedCopy = 0 {0} {1}", globalFilterWhere, fieldFilterWhere);
if (pageSize == 0)
{
pageSize = int.MaxValue;
}
var commandText = string.Format(CultureInfo.InvariantCulture, sql, where, pageNumber * pageSize, pageSize);
using (var cmd = new SqlCommand(commandText, cn))
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
result.Add(
new ProcessInfoDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Description = reader.GetString(2),
Icon = AdoHelper.ReadImage(reader, 3),
IsPublished = reader.GetInt32(4) != 0,
SystemName = reader.GetString(5),
Guid = reader.GetGuid(6),
ProcessColor = reader.GetInt64(7),
IsInactive = reader.GetBool(8),
IsSystem = reader.GetBool(9),
ProcessDocumentation = reader.GetString(10),
});
}
}
sql = string.Format(CultureInfo.InvariantCulture, "SELECT TotalRowCount = (SELECT COUNT(t0.Id) FROM Processes [t0] {0}), TotalActiveRowCount = (SELECT COUNT(t0.Id) FROM dbo.Processes t0 {0} AND t0.IsInactive = 0 AND t0.SimpleProcess = 0)", where);
using (var cmd = new SqlCommand(sql, cn))
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
result.TotalRowCount = reader.GetInt32(0);
result.TotalActiveRowCount = reader.GetInt32(1);
}
}
}
return result;
}
/// <summary>
/// Retrieves cross ref required 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="CrossRefRequiredStepDto" />.</returns>
public CrossRefRequiredStepDto FetchCrossRefRequiredStep(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].[GetCrossRefRequiredStep] @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 CrossRefRequiredStepDto
{
Id = reader.GetInt32(0),
CrossRefProcessId = reader.GetNullableInt(1),
FieldId = reader.GetInt32(2),
DisplayFieldName = reader.GetString(3),
AllowMultiple = reader.GetBoolean(4),
EditViaSpreadsheet = reader.GetBoolean(5),
MultiCrAllowLinkUnlink = reader.GetBoolean(6),
MultiCrAllowAddNew = reader.GetBoolean(7),
IsOwner = reader.GetBoolean(8),
AllowRecentVersion = reader.GetBoolean(9),
LinkFieldSystemName = reader.GetString(10)
};
if (reader.NextResult())
{
while (reader.Read())
{
var df = new CrossReferenceDisplayFieldDto
{
Id = reader.GetInt32(0),
DisplayName = reader.GetString(1),
FullPath = reader.GetString(2),
Order = reader.GetDouble(3),
FieldGuid = reader.GetGuid(4)
};
dto.DisplayFields.Add(df);
}
}
return dto;
}
}
}
}
return null;
}
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>
/// Retrieves eSync process list.
/// </summary>
/// <param name="processGuid">The process GUID.</param>
/// <param name="isPublishedCopy">The is published copy.</param>
/// <returns>The <see cref="IList" />.</returns>
public IEnumerable<ESyncProcessInfoDto> FetchESyncProcessList(Guid processGuid, bool isPublishedCopy)
{
const string CommandText = @"
SELECT sp.[Id] ,
sp.[Guid] ,
sp.[Name]
FROM [dbo].[SyncProcesses] sp
INNER JOIN [dbo].[Processes] p ON p.[Id] = sp.[ProcessId]
AND p.[IsRemoved] = 0
WHERE p.[Guid] = @processGuid
AND p.[IsPublishedCopy] = @isPublishedCopy;";
using (var cmd = new SqlCommand(CommandText))
{
cmd.Parameters.AddWithValue("@processGuid", processGuid);
cmd.Parameters.AddWithValue("@isPublishedCopy", isPublishedCopy);
var result = new List<ESyncProcessInfoDto>();
Database.GetDataReader(
cmd,
dr =>
{
using (var reader = new SafeDataReader(dr))
{
while (reader.Read())
{
var dto = new ESyncProcessInfoDto
{
Id = reader.GetInt32(0),
Guid = reader.GetGuid(1),
Name = reader.GetString(2)
};
result.Add(dto);
}
}
});
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>
/// Retrieves display field.
/// </summary>
/// <param name="processId">The process id.</param>
/// <param name="displayField">The display field.</param>
/// <returns>The <see cref="FieldInfoDto" />.</returns>
public FieldInfoDto FetchDisplayField(int processId, string displayField)
{
const string CommandText = @"
WITH ProcessList ( SystemName, Id, PublishedProcessId, IdOfPublishedProcess, LEVEL, IsBase )
AS ( SELECT p.SystemName ,
p.Id AS Id ,
pp.Id AS PublishedProcessId ,
pp.ProcessId IdOfPublishedProcess ,
0 AS LEVEL ,
0 AS IsBase
FROM Processes p
LEFT OUTER JOIN PublishedProcesses pp ON pp.Id = p.BaseProcessId
WHERE p.Id = @processId
UNION ALL
SELECT p1.SystemName ,
pp.ProcessId ,
pp.Id AS PublishedProcessId ,
pp.ProcessId IdOfPublishedProcess ,
LEVEL + 1 ,
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.Id ,
f.Name ,
f.SystemName ,
f.FieldTypeId ,
pl.IsBase ,
pl.SystemName ,
CASE WHEN f.[FieldTypeId] = 6
AND ISNULL(crrs.AllowMultiple, 0) = 1
THEN 'MultiReference'
ELSE ft.[DataType]
END ,
f.Guid ,
CASE WHEN LEN(ISNULL(CAST(es.[CalculatedExpression] AS NVARCHAR(MAX)),
'')) > 0 THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS 'IsCalculated' ,
( SELECT CAST(CASE WHEN EXISTS ( SELECT *
FROM [dbo].[RequiredRuleConfigFields] rrcf
INNER JOIN [dbo].[RequiredRuleConfigs] rrc ON rrc.[Id] = rrcf.[RequiredConfigId]
WHERE rrc.[ProcessId] = pl.[Id]
AND rrcf.[FieldGuid] = f.[Guid] )
THEN 1
ELSE 0
END AS BIT)
) AS [IsRequired]
FROM Fields f
INNER JOIN Sections s ON s.Id = f.SectionId
INNER JOIN ProcessList pl ON s.ProcessId = pl.Id
INNER JOIN [dbo].[FieldTypes] ft ON ft.[Id] = f.[FieldTypeId]
LEFT JOIN [dbo].[CrossRefRequredFieldStep] crrs ON crrs.[FieldId] = f.[Id]
AND f.[FieldTypeId] = 6
LEFT JOIN [dbo].[stepExpressions] es ON es.[FieldId] = f.[Id]
WHERE f.SystemName = @displayField
";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(CommandText, cn))
{
cmd.Parameters.AddWithValue("@processId", processId);
cmd.Parameters.AddWithValue("@displayField", displayField);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
return new FieldInfoDto
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
SystemName = reader.GetString(2),
FieldTypeId = reader.GetInt32(3),
DefinedIn = reader.GetString(5),
DataType = reader.GetString(6),
Guid = reader.GetGuid(7),
IsCalculated = reader.GetBoolean(8),
IsRequired = reader.GetBoolean(9)
};
}
}
}
}
return null;
}
/// <summary>
/// Updates ActiveProcesses table in runtime db.
/// </summary>
/// <remarks>Active processes are those not being removed or inactive.</remarks>
public override void UpdateActiveProcesses()
{
try
{
var values = new List<string>();
const string commandText = @"
SELECT p.Id, p.Name, p.SystemName, p.Guid, p.IconId AS [Icon]
FROM PublishedProcesses pp
INNER JOIN Processes p ON p.Id = pp.ProcessId
WHERE p.IsRemoved = 0 AND p.IsInactive = 0
";
using (var ctx = GetMetaDatabaseConnectionManager())
{
using (var cmd = new SqlCommand(commandText, ctx.Connection))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
values.Add(string.Format("({0},'{1}','{2}','{3}',{4})", reader.GetInt32(0), reader.GetString(1), reader.GetString(2),
reader.GetGuid(3).ToString("D"), reader.IsDBNull(4) ? "NULL" : reader.GetInt32(4).ToString(CultureInfo.InvariantCulture)));
}
}
}
}
var script = string.Format(@"
IF NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = '{0}')
RETURN;
SET IDENTITY_INSERT __Process ON
MERGE INTO [dbo].[__Process] AS TARGET USING (
VALUES {1}
)
AS Source([Id], [Name], [SystemName], [ProcessID], [Icon]) ON TARGET.id = Source.Id -- update matched rows
WHEN MATCHED THEN
UPDATE
SET [Name] = Source.[Name],
[SystemName] = Source.[SystemName],
[ProcessID] = Source.[ProcessID],
[Icon] = Source.[Icon],
[IsRemoved] = 0 -- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Id],
[Name],
[SystemName],
[ProcessID],
[Icon],
[IsRemoved])
VALUES ([Id],
[Name],
[SystemName],
[ProcessID],
[Icon],
0)
-- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;
SET IDENTITY_INSERT __Process OFF
", Constants.ProcessProcessName, string.Join(",", values));
ExecuteSql(script);
}
catch (SqlException ex)
{
Logger.Log(LogSeverity.Error, "SqlServerDatabaseGenerator", ex);
}
}
public IPagedList<PublishedProcessInfoDTO> FetchPublishedProcesses(string filter, int pageNumber, int pageSize, string filterExpression)
{
var result = new PagedList<PublishedProcessInfoDTO>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
const string Sql = @"
DECLARE @p0 AS INT
SET @p0 = {1}
DECLARE @p1 AS INT
SET @p1 = {2};
SELECT [t1].[Id],
[t1].[Name],
[t1].[SystemName],
[t1].[IconId],
[t1].[Guid]
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY [t0].[Name], [t0].[Id]) AS
[ROW_NUMBER],
[t0].[Name],
[t0].SystemName,
[t0].[Guid],
[t0].Id,
[i].Id as IconId
FROM [dbo].[Processes] AS [t0]
JOIN [dbo].PublishedProcesses AS [t1] ON [t1].ProcessId = [t0].Id
LEFT OUTER JOIN Icons i ON [t0].IconId = [i].Id
LEFT OUTER JOIN Colors c ON [t0].ColorId = [c].Id
{0}
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY
[t1].[ROW_NUMBER]
";
var fieldFilterWhere = AdoHelper.BuildFilterStatement(FilterDescriptor.GetFilterList(filterExpression), "Processes", tableAlias: "[t0]");
var globalFilterWhere = AdoHelper.BuildFilterStatement(filter, new[] { "[t0].Name" });
var where = string.Format(CultureInfo.InvariantCulture, "WHERE IsRemoved = 0 {0} {1}", globalFilterWhere, fieldFilterWhere);
if (pageSize == 0)
{
pageSize = int.MaxValue;
}
var commandText = string.Format(CultureInfo.InvariantCulture, Sql, where, pageNumber * pageSize, pageSize);
using (var cmd = new SqlCommand(commandText, cn))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
result.Add(
new PublishedProcessInfoDTO
{
ProcessId = reader.GetInt32(0),
Name = reader.GetString(1),
SystemName = reader.GetString(2),
IconId = reader.GetInt32(3),
ProcessGuid = reader.GetGuid(4),
});
}
}
}
using (
var cmdCount =
new SqlCommand(
string.Format(
CultureInfo.InvariantCulture,
"SELECT COUNT([t0].Id) FROM Processes [t0] JOIN [dbo].PublishedProcesses AS [t1] ON [t1].ProcessId = [t0].Id {0}",
where),
cn))
{
result.TotalRowCount = (int)cmdCount.ExecuteScalar();
}
}
return result;
}
/// <summary>
/// Reads required rule configurations.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private static void ReadRequiredRuleConfigs(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
while (sr.Read())
{
var configDto = new RequiredRuleConfigEditDto
{
Id = sr.GetInt32(0),
ProcessId = sr.GetInt32(1),
Guid = sr.GetGuid(2),
StateGuid = sr.GetGuid(3)
};
process.RequiredRuleConfigs.Add(configDto);
}
ReadRequiredRuleConfigFields(process, sr);
}
public EDCEditDto FetchEdcBySystemName(string systemname)
{
const string sql = @"
SELECT [Id]
,[Name]
,[SystemName]
,[Documentation]
,[GuidId]
FROM [dbo].[ExternalDataConfiguration] edc
WHERE [SystemName] = @sysName
";
var result = new EDCEditDto();
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("@sysName", systemname);
using (var sr = new SafeDataReader(cmd.ExecuteReader()))
{
while (sr.Read())
{
var parameter = new EDCEditDto
{
Id = sr.GetInt32(0),
Name = sr.GetString(1),
SystemName = sr.GetString(2),
Documentation = sr.GetString(3),
Guid = sr.GetGuid(4)
};
result = parameter;
}
}
result.ConnectionList = new List<EDCConnectionEditDto>();
result.DataVariableList = new List<EDCDataVariablesEditDto>();
FetchEDCConnectionList(cn, result);
FetchDataVariableList(cn, result);
}
}
return result;
}
public IPagedList<PublishedProcessInfoDTO> FetchAvailablePublishedProcesses(string filter, int pageNumber, int pageSize, string filterExpression, bool isAdmin, IEnumerable<int> personRoles)
{
var result = new PagedList<PublishedProcessInfoDTO>();
var roles = new List<int> { Constants.AllRolesId };
if (personRoles != null)
{
roles.AddRange(personRoles);
}
var rolesString = string.Join(",", roles.Select(r => r.ToString(CultureInfo.InvariantCulture)));
var adminFilter = string.Format(
CultureInfo.InvariantCulture,
@"WHERE ISNULL(p.[IsRemoved], 0) = 0 AND ISNULL(p.IsInactive, 0) = 0");
const string NonAdminFilterFormat = @"
WHERE
(
EXISTS
(
SELECT nisc.[Id]
FROM [dbo].[NavigationItemSecurityConfigurations] nisc
WHERE nisc.[NavigationItemId] = ni.Id AND nisc.[CanView] = 1 AND nisc.[RoleId] IN ({0})
)
AND
ISNULL(p.[IsRemoved], 0) = 0 AND ISNULL(p.IsInactive, 0) = 0
)
OR
ng.[SystemName] = '{1}'
";
var nonAdminFilter = string.Format(
CultureInfo.InvariantCulture,
NonAdminFilterFormat,
rolesString,
Constants.AdminMenu);
var whereString = isAdmin ? adminFilter : nonAdminFilter;
const string IdsQueryFormat = @"
SELECT
pp.[ProcessId]
FROM
[dbo].[NavigationGroups] ng
INNER JOIN [dbo].[NavigationItems] ni
ON ng.[Id] = ni.[NavigationGroupId]
INNER JOIN [dbo].[PublishedProcesses] pp
ON pp.[Id] = ni.[PublishedProcessId]
INNER JOIN [dbo].[Processes] p
ON p.[Id] = pp.[ProcessId]
{0}";
var idsQuery = string.Format(
CultureInfo.InvariantCulture,
IdsQueryFormat,
whereString);
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
const string Sql =
@"
DECLARE @p0 AS INT
SET @p0 = {1}
DECLARE @p1 AS INT
SET @p1 = {2};
SELECT [t1].[Id],
[t1].[Name],
[t1].[SystemName],
[t1].[IconId],
[t1].[Guid]
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY [t0].[Name], [t0].[Id]) AS
[ROW_NUMBER],
ISNULL(pl.ProcessName,[t0].[Name]) AS [Name],
[t0].SystemName,
[t0].[Guid],
[t0].Id,
[i].Id as IconId
FROM [dbo].[Processes] AS [t0]
JOIN [dbo].PublishedProcesses AS [t1] ON [t1].ProcessId = [t0].Id
INNER JOIN Processes p2 ON [t0].Guid = p2.Guid AND p2.IsPublishedCopy = 0
LEFT OUTER JOIN Icons i ON [t0].IconId = [i].Id
LEFT OUTER JOIN Colors c ON [t0].ColorId = [c].Id
LEFT OUTER JOIN dbo.Localizations l ON l.CultureName = '{3}'
LEFT OUTER JOIN dbo.ProcessLocalizations pl ON pl.LocalizationId = l.Id AND p2.Id = pl.ProcessId
{0}
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY
[t1].[ROW_NUMBER]
";
var fieldFilterWhere = AdoHelper.BuildFilterStatement(FilterDescriptor.GetFilterList(filterExpression), "Processes", tableAlias: "[t0]");
var globalFilterWhere = AdoHelper.BuildFilterStatement(filter, new[] { "[t0].Name" });
var where = string.Format(CultureInfo.InvariantCulture, "WHERE [t0].IsRemoved = 0 {0} {1} AND [t1].[ProcessId] IN ({2})", globalFilterWhere, fieldFilterWhere, idsQuery);
if (pageSize == 0)
{
pageSize = int.MaxValue;
}
var commandText = string.Format(CultureInfo.InvariantCulture, Sql, where, pageNumber * pageSize, pageSize, System.Threading.Thread.CurrentThread.CurrentUICulture.Name);
using (var cmd = new SqlCommand(commandText, cn))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
result.Add(
new PublishedProcessInfoDTO
{
ProcessId = reader.GetInt32(0),
Name = reader.GetString(1),
SystemName = reader.GetString(2),
IconId = reader.GetInt32(3),
ProcessGuid = reader.GetGuid(4),
});
}
}
}
commandText =
string.Format(
CultureInfo.InvariantCulture,
"SELECT COUNT([t0].Id) FROM Processes [t0] JOIN [dbo].PublishedProcesses AS [t1] ON [t1].ProcessId = [t0].Id {0}",
where);
using (var cmdCount = new SqlCommand(commandText, cn))
{
result.TotalRowCount = (int)cmdCount.ExecuteScalar();
}
}
return result;
}
public override void CopyNavigationMenu()
{
var values = new List<string>();
const string commandText = @"
SELECT Id, Name, SystemName, LastModifiedOn, IconURL, Sequence, IconId from NavigationGroups
";
using (var ctx = GetMetaDatabaseConnectionManager())
{
using (var cmd = new SqlCommand(commandText, ctx.Connection))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
values.Add(string.Format("({0},'{1}','{2}','{3}','{4}',{5},{6})", reader.GetInt32(0), reader.GetString(1), reader.GetString(2),
reader.GetDateTime(3).ToString("yyyy-MM-dd HH:mm:ss"), reader.GetString(4), reader.GetDouble(5), reader.IsDBNull(6) ? "NULL" : reader.GetInt32(6).ToString()));
}
}
}
}
var script = string.Format(@"
SET IDENTITY_INSERT __NavigationGroup ON
MERGE INTO [dbo].[__NavigationGroup] AS TARGET USING (
VALUES {0}
)
AS Source([Id], [Name], [SystemName], [LastModifiedOn], [IconURL], [Sequence],[Icon]) ON TARGET.id = Source.Id -- update matched rows
WHEN MATCHED THEN
UPDATE
SET [Name] = Source.[Name],
[SystemName] = Source.[SystemName],
[LastModifiedOn] = Source.[LastModifiedOn],
[IconURL] = Source.[IconURL],
[Sequence] = Source.[Sequence],
[Icon] = Source.[Icon],
[IsRemoved] = 0 -- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Id],
[Name],
[SystemName],
[LastModifiedOn],
[IconURL],
[Sequence],
[Icon],
[IsRemoved])
VALUES ([Id],
[Name],
[SystemName],
[LastModifiedOn],
[IconURL],
[Sequence],
[Icon],
0)
-- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;
SET IDENTITY_INSERT __NavigationGroup OFF
", string.Join(",", values));
ExecuteSql(script);
values.Clear();
const string navItemsCommandText = @"
SELECT ni.id,
ni.guid,
ni.NAME,
p.systemname,
NULL,
ni.lastmodifiedon,
sequence,
navigationgroupid,
ni.[description],
c.[Color],
ni.IconId
FROM [dbo].[navigationitems] ni
INNER JOIN publishedprocesses pp
ON pp.id = ni.publishedprocessid
INNER JOIN processes p
ON pp.processid = p.id AND p.IsPublishedCopy = 1
LEFT OUTER JOIN colors as c on c.Id = p.[ColorId]
WHERE p.IsRemoved = 0
";
using (var ctx = GetMetaDatabaseConnectionManager())
{
using (var cmd = new SqlCommand(navItemsCommandText, ctx.Connection))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
values.Add(string.Format(CultureInfo.InvariantCulture, "({0}, '{1}', '{2}', '{3}', '{4}', '{5}', {6}, {7}, '{8}', {9}, {10})", reader.GetInt32(0), reader.GetGuid(1), Escape(reader.GetString(2)), reader.GetString(3), reader.GetString(4),
reader.GetDateTime(5).ToString("yyyy-MM-dd HH:mm:ss"), reader.GetDouble(6), reader.GetInt32(7), Escape(reader.GetString(8)), reader.GetInt64(9), reader.IsDBNull(10) ? "null" : reader.GetInt32(10).ToString()));
}
}
}
}
if (values.Count == 0)
return;
script = string.Format(@"
SET IDENTITY_INSERT __NavigationItem ON
MERGE INTO [dbo].[__NavigationItem] AS TARGET USING (
VALUES {0}
)
AS Source([Id], [Guid], [Name], [SystemName], [IconURL], [LastModifiedOn], [Sequence], [NavigationGroupId], [Description], [ProcessColor], [IconId]) ON TARGET.id = Source.Id -- update matched rows
WHEN MATCHED THEN
UPDATE
SET [Guid] = Source.[Guid],
[Name] = Source.[Name],
[SystemName] = Source.[SystemName],
[IconURL] = Source.[IconURL],
[LastModifiedOn] = Source.[LastModifiedOn],
[NavigationGroup] = Source.[NavigationGroupId],
[Description] = Source.[Description],
[Sequence] = Source.[Sequence], -- insert new rows
[ProcessColor] = Source.[ProcessColor],
[Icon] = Source.[Iconid]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Id],
[Guid],
[Name],
[SystemName],
[IconURL],
[LastModifiedOn],
[Sequence],
[NavigationGroup],
[Description],
[ProcessColor],
[IsRemoved],
[Icon])
VALUES ([Id],
[Guid],
[Name],
[SystemName],
[IconURL],
[LastModifiedOn],
[Sequence],
[NavigationGroupId],
[Description],
[ProcessColor],
0,
[IconId])
-- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;
SET IDENTITY_INSERT __NavigationItem OFF ", string.Join(",", values));
ExecuteSql(script);
//------------------------------------------------------
values.Clear();
const string navigationGroupSecurityConfigurationsScript = @"
SELECT ngscs.[id],
ngscs.[NavigationGroupId],
ngscs.[RoleId],
ngscs.[CanView]
FROM [dbo].[NavigationGroupSecurityConfigurations] ngscs";
using (var ctx = GetMetaDatabaseConnectionManager())
{
using (var cmd = new SqlCommand(navigationGroupSecurityConfigurationsScript, ctx.Connection))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
values.Add(string.Format(CultureInfo.InvariantCulture, "({0}, '{1}', '{2}', '{3}')", reader.GetInt32(0),reader.GetInt32(1), reader.GetInt32(2), reader.GetBoolean(3)));
}
}
}
}
if (values.Count == 0)
return;
script = string.Format(@"SET IDENTITY_INSERT __NavigationGroupSecurity ON
MERGE INTO [dbo].[__NavigationGroupSecurity] AS TARGET USING (
VALUES {0} )
AS Source([Id], [NavigationGroup], [RoleId], [CanView]) ON TARGET.id = Source.Id -- update matched rows
WHEN MATCHED THEN
UPDATE
SET [NavigationGroup] = Source.[NavigationGroup],
[RoleId] = Source.[RoleId],
[CanView] = Source.[CanView]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Id],
[NavigationGroup],
[RoleId],
[CanView],
[IsRemoved])
VALUES ([Id],
[NavigationGroup],
[RoleId],
[CanView],
0)
-- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;
SET IDENTITY_INSERT __NavigationGroupSecurity OFF ", string.Join(",", values));
ExecuteSql(script);
//------------------------------------------------------
values.Clear();
const string navigationItemsSecurityConfigurationsScript = @"
SELECT ngscs.[id],
ngscs.[NavigationItemId],
ngscs.[RoleId],
ngscs.[CanView]
FROM [dbo].[NavigationItemSecurityConfigurations] ngscs";
using (var ctx = GetMetaDatabaseConnectionManager())
{
using (var cmd = new SqlCommand(navigationItemsSecurityConfigurationsScript, ctx.Connection))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
values.Add(string.Format(CultureInfo.InvariantCulture, "({0}, '{1}', '{2}', '{3}')", reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetBoolean(3)));
}
}
}
}
if (values.Count == 0)
return;
script = string.Format(@"SET IDENTITY_INSERT __NavigationItemSecurity ON
MERGE INTO [dbo].[__NavigationItemSecurity] AS TARGET USING (
VALUES {0} )
AS Source([Id], [NavigationItem], [RoleId], [CanView]) ON TARGET.id = Source.Id -- update matched rows
WHEN MATCHED THEN
UPDATE
SET [NavigationItem] = Source.[NavigationItem],
[RoleId] = Source.[RoleId],
[CanView] = Source.[CanView]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Id],
[NavigationItem],
[RoleId],
[CanView],
[IsRemoved])
VALUES ([Id],
[NavigationItem],
[RoleId],
[CanView],
0)
-- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;
SET IDENTITY_INSERT __NavigationItemSecurity OFF ", string.Join(",", values));
ExecuteSql(script);
}
/// <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;
}