/// <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 IList<LocalizationDto> FetchLocalizationList()
{
var result = new List<LocalizationDto>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
var commandText = string.Format(CultureInfo.InvariantCulture,
@"
SELECT loc.[Id],
loc.[LocalName],
loc.[EnglishName],
loc.[CultureName],
loc.[IconId],
loc.[IsRightToLeft],
loc.[IsDefault]
FROM [dbo].[Localizations] loc
ORDER BY loc.EnglishName");
using (var cmd = new SqlCommand(commandText, connection))
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
while (reader.Read())
{
var dto = new LocalizationDto
{
Id = reader.GetInt32(0),
LocalName = reader.GetString(1),
EnglishName = reader.GetString(2),
CultureName = reader.GetString(3),
IconId = reader.GetInt32(4),
IsRightToLeft = reader.GetBoolean(5),
IsDefault = reader.GetBoolean(6)
};
result.Add(dto);
}
}
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)
});
}
}
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>
/// 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>
/// 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>
/// Retrieves reverse cross ref field.
/// </summary>
/// <param name="fieldId">The field id.</param>
/// <returns>The <see cref="ReverseCrossRefFieldDto" />.</returns>
public ReverseCrossRefFieldDto FetchReverseCrossRefField(int fieldId)
{
const string CmdText =
@"
SELECT
f.[Id]
,f.[Name]
,f.[SystemName]
,x.[DisplayMultiple]
,x.[ReverseCrossRefProcessId]
,p.[SystemName]
,x.[CrossRefFieldName]
,x.[DisplayFieldName]
,o.[ShowLatestVersion]
FROM [dbo].[Fields] f
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] AND p.[IsRemoved] = 0
LEFT OUTER JOIN [dbo].[ReverseCrossRefOptionsFieldStep] o ON o.[FieldId] = f.[Id]
WHERE f.[Id] = @fieldId";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(CmdText, cn))
{
cmd.Parameters.AddWithValue("@fieldId", fieldId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
return new ReverseCrossRefFieldDto
{
FieldId = reader.GetInt32(0),
Name = reader.GetString(1),
SystemName = reader.GetString(2),
DisplayMultiple = reader.GetBoolean(3),
ReferenceProcessId = reader.GetInt32(4),
ReferenceProcessSystemName = reader.GetString(5),
ReferenceField = reader.GetString(6),
DisplayField = reader.GetString(7),
ShowLatestVersion = reader.GetNullableBool(8) ?? false
};
}
}
}
}
return new ReverseCrossRefFieldDto();
}
/// <summary>
/// Retrieves cross ref field.
/// </summary>
/// <param name="fieldId">The field id.</param>
/// <returns>The <see cref="CrossRefFieldDto" />.</returns>
public CrossRefFieldDto FetchCrossRefField(int fieldId)
{
const string CmdText =
@"
SELECT f.[Name]
, f.[SystemName]
, x.[AllowMultiple]
, x.[CrossRefProcessId]
, x.[LinkFieldSystemName]
, x.[DisplayFieldName]
, p.[SystemName]
FROM [dbo].[Fields] f
INNER JOIN [dbo].[CrossRefRequredFieldStep] x ON f.[Id] = x.[FieldId]
LEFT OUTER JOIN [dbo].[PublishedProcesses] pp ON pp.[Id] = x.[CrossRefProcessId]
LEFT OUTER JOIN [dbo].[Processes] p ON p.[Id] = pp.[ProcessId]
WHERE f.[Id] = @fieldId";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(CmdText, cn))
{
cmd.Parameters.AddWithValue("@fieldId", fieldId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
return new CrossRefFieldDto
{
Name = reader.GetString(0),
SystemName = reader.GetString(1),
AllowMany = reader.GetBoolean(2),
CrossRefProcessId = reader.GetInt32(3),
LinkFieldSystemName = reader.GetString(4),
DisplayField = reader.GetString(5),
ProcessSystemName = reader.GetString(6)
};
}
}
}
}
return new CrossRefFieldDto();
}
/// <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;
}
private PaymentInfoDto Fetch(SafeDataReader dr)
{
var paymentInfo = new PaymentInfoDto();
paymentInfo.Id = dr.GetInt32("tran_id");
paymentInfo.BatchNum = dr.GetInt32("batch_num");
paymentInfo.PayDate = dr.GetSmartDate("pay_date");
paymentInfo.JobNum = dr.GetInt32("job_num");
paymentInfo.ThankYou1 = dr.GetString("thank_you_1");
paymentInfo.StudyTopic = dr.GetString("study_topic");
paymentInfo.ThankYou2 = dr.GetString("thank_you_2");
paymentInfo.MarketingResearchMessage = dr.GetString("marketing_research_message");
paymentInfo.CheckDate = dr.GetSmartDate("check_date");
paymentInfo.CheckNum = dr.GetString("check_num");
paymentInfo.Amount = dr.GetDecimal("tran_amount");
paymentInfo.PersonId = dr.GetString("person_id");
paymentInfo.Prefix = dr.GetString("name_prefix");
paymentInfo.FirstName = dr.GetString("first_name");
paymentInfo.MiddleName = dr.GetString("middle_name");
paymentInfo.LastName = dr.GetString("last_name");
paymentInfo.NameSuffix = dr.GetString("name_suffix");
paymentInfo.Title = dr.GetString("title");
paymentInfo.Company = dr.GetString("company");
paymentInfo.Address1 = dr.GetString("address_1");
paymentInfo.Address2 = dr.GetString("address_2");
paymentInfo.Municipality = dr.GetString("municipality");
paymentInfo.Region = dr.GetString("region");
paymentInfo.PostalCode = dr.GetString("postal_code");
paymentInfo.Country = dr.GetString("country");
paymentInfo.Email = dr.GetString("email");
paymentInfo.PhoneNumber = dr.GetString("phone_number");
paymentInfo.Completed = dr.GetBoolean("print_flag");
return paymentInfo;
}
/// <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;
}
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;
}
public Profile Map(SafeDataReader reader)
{
var item = (Profile)Activator.CreateInstance(typeof(Profile), true);
using (BypassPropertyChecks(item))
{
item.UniqueID = reader.GetInt32("UniqueID");
item.Username = reader.GetString("Username");
item.ApplicationName = reader.GetString("ApplicationName");
item.IsAnonymous = reader.IsDBNull("IsAnonymous") ? (System.Boolean?)null : reader.GetBoolean("IsAnonymous");
item.LastActivityDate = reader.IsDBNull("LastActivityDate") ? (System.DateTime?)null : reader.GetDateTime("LastActivityDate");
item.LastUpdatedDate = reader.IsDBNull("LastUpdatedDate") ? (System.DateTime?)null : reader.GetDateTime("LastUpdatedDate");
}
MarkOld(item);
return item;
}
/// <summary>
/// Read reports.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private static void ReadReports(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
while (sr.Read())
{
var reportDto = new ProcessReportEditDto
{
Id = sr.GetInt32(0),
Guid = sr.GetGuid(1),
Title = sr.GetString(2),
Description = sr.GetString(3),
FileName = sr.GetString(4),
DisplayToUsers = sr.GetBoolean(5),
Visibility = (ReportVisibility)Enum.Parse(typeof(ReportVisibility), sr.GetString(6), true),
IsSecured = sr.GetBoolean(7)
};
process.ReportList.Add(reportDto);
}
ReadReportSecurityConfigurations(process, sr);
}
/// <summary>
/// Retrieves published process by process name.
/// </summary>
/// <param name="processName">The process name.</param>
/// <returns>The <see cref="PublishedProcessInfoDTO" />.</returns>
public PublishedProcessInfoDTO FetchPublishedProcess(string processName)
{
var result = new PublishedProcessInfoDTO();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
const string Sql =
@"
SELECT pp.Id
,Name
,p.Id AS ProcessId
,p.SystemName
,p.BaseProcessId
,p.ProcessOption
,p.SimpleProcess
,p.IsStateEnabled
FROM [dbo].[PublishedProcesses] pp
INNER JOIN Processes p ON pp.ProcessId = p.Id
WHERE p.SystemName = @procName AND p.IsRemoved=0";
using (var cmd = new SqlCommand(Sql, cn))
{
cmd.Parameters.AddWithValue("@procName", processName);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
result = new PublishedProcessInfoDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
ProcessId = reader.GetInt32(2),
SystemName = reader.GetString(3),
ProcessOption =
!string.IsNullOrEmpty(reader.GetString(5))
? (ProcessOption)
Enum.Parse(
typeof(ProcessOption), reader.GetString(5))
: ProcessOption.None,
SimpleProcess = reader.GetBool(6),
IsStateEnabled = reader.GetBoolean(7)
};
if (!reader.IsDBNull(4))
{
result.BaseProcess = this.FetchPublishedProcess(reader.GetInt32(4));
}
}
}
}
}
return result;
}
/// <summary>
/// Retrieve steps.
/// </summary>
/// <param name="fieldTypeId">The field type id.</param>
/// <returns>The <see cref="IList" />.</returns>
public IList<StepDto> FetchSteps(int fieldTypeId)
{
var list = new List<StepDto>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
const string CommandText =
@"
SELECT [Id],
[Name],
[Class],
[ViewClass],
fft.StepNumber,
[DefinesFieldType],
[ViewModelClass]
FROM [dbo].[ProcessFieldEditors] e
INNER JOIN FieldEditorsFieldTypes fft
ON fft.FieldEditorId = e.Id
WHERE fft.FieldTypeId = @p_id
ORDER BY fft.StepNumber";
using (var command = new SqlCommand(CommandText, connection))
{
command.Parameters.AddWithValue("@p_Id", fieldTypeId);
using (var reader = new SafeDataReader(command.ExecuteReader()))
{
while (reader.Read())
{
list.Add(new StepDto
{
StepId = reader.GetInt32(0),
Name = reader.GetString(1),
Class = reader.GetString(2),
ViewClass = reader.GetString(3),
StepNumber = reader.GetInt32(4),
DefinesFieldType = reader.GetBoolean(5),
ViewModelClass = reader.GetString(6),
});
}
}
}
}
return list;
}
/// <summary>
/// Adds the navigation item.
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="currentGroup">The current group.</param>
private static void AddNavigationItem(SafeDataReader reader, NavigationGroupDTO currentGroup)
{
var navItemDto = new NavigationItemDTO
{
Id = reader.GetInt32(5),
Name = reader.GetString(6),
SystemName = reader.GetString(7),
IconId = reader.GetNullableInt(8),
IconUrl = reader.GetString(9),
AssemblyName = reader.GetString(10),
ProcessId = reader.GetNullableInt(11),
ProcessSystemName = reader.GetString(13),
Description = reader.GetString(17)
};
navItemDto.Description = navItemDto.ProcessId == null ? reader.GetString("HelpText") : reader.GetString(reader.GetOrdinal("Description"));
navItemDto.BackgroundColor = reader.GetInt64(reader.GetOrdinal("BackgroundColor"));
navItemDto.ProcessViewGuid = reader.GetNullableGuid(reader.GetOrdinal("ProcessViewGuid"));
navItemDto.IsSystem = reader.GetNullableBool(reader.GetOrdinal("IsSystem"));
navItemDto.IsBeta = reader.GetBoolean(reader.GetOrdinal("IsBeta"));
var processName = reader.GetString(12);
if (!string.IsNullOrEmpty(processName))
navItemDto.Name = processName;
currentGroup.NavigationItems.Add(navItemDto);
}
/// <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 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 cross-reference field info.
/// </summary>
/// <param name="processName">
/// The name of the process that defines the cross-reference field.
/// </param>
/// <param name="fieldName">
/// The cross-reference field name.
/// </param>
/// <returns>
/// The <see cref="CrossRefFieldDto"/>.
/// </returns>
public CrossRefFieldDto FetchCrossRefField(string processName, string fieldName)
{
const string CommandText = @"
SELECT f.[Name]
, f.[SystemName]
, x.[AllowMultiple]
, x.[CrossRefProcessId]
, x.[LinkFieldSystemName]
FROM [dbo].[Fields] f
INNER JOIN [dbo].[CrossRefRequredFieldStep] x ON f.[Id] = x.[FieldId]
INNER JOIN [dbo].[Sections] s ON s.[Id] = f.[SectionId]
INNER JOIN [dbo].[Processes] p ON p.[Id] = s.[ProcessId]
WHERE p.[IsRemoved] = 0 AND p.[SystemName] = @processName AND p.[IsPublishedCopy] = 1 AND f.[IsRemoved] = 0 AND f.[SystemName] = @fieldName";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(CommandText, cn))
{
cmd.Parameters.AddWithValue("@processName", processName);
cmd.Parameters.AddWithValue("@fieldName", fieldName);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
return new CrossRefFieldDto
{
Name = reader.GetString(0),
SystemName = reader.GetString(1),
AllowMany = reader.GetBoolean(2),
CrossRefProcessId = reader.GetInt32(3),
LinkFieldSystemName = reader.GetString(4)
};
}
}
}
}
return null;
}
/// <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 reverse 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="ReverseCrossRefRequiredStepDto" />.</returns>
public ReverseCrossRefRequiredStepDto FetchReverseCrossRefRequiredStep(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].[GetReverseCrossRefRequiredStep] @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 ReverseCrossRefRequiredStepDto
{
Id = reader.GetInt32(0),
FieldId = reader.GetInt32(1),
ReverseCrossRefProcessId = reader.GetNullableInt(2),
CrossRefFieldName = reader.GetString(3),
DisplayFieldName = reader.GetString(4),
DisplayMultiple = reader.GetBoolean(5)
};
if (reader.NextResult())
{
while (reader.Read())
{
var df = new ReverseCrossReferenceDisplayFieldDto
{
Id = reader.GetInt32(0),
DisplayName = reader.GetString(1),
FullPath = reader.GetString(2),
Order = reader.GetDouble(3)
};
dto.SelectedDisplayFields.Add(df);
}
}
return dto;
}
}
}
}
return null;
}
/// <summary>
/// Retrieves field types.
/// </summary>
/// <returns>The <see cref="IList" />.</returns>
public IList<FieldTypeDto> FetchFieldTypes()
{
var result = new List<FieldTypeDto>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
const string CommandText = "SELECT Id, Name, DataType, CanBeRequired FROM [dbo].[FieldTypes] WHERE DataType != 'Gantt' ORDER BY Name";
using (var cmd = new SqlCommand(CommandText, connection))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
result.Add(
new FieldTypeDto
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
DataType = reader.GetString(2),
CanBeRequired = reader.GetBoolean(3)
});
}
}
}
}
return result;
}
/// <summary>
/// Retrieves the picture step.
/// </summary>
/// <param name="processName">
/// The process name.
/// </param>
/// <param name="fieldName">
/// The field name.
/// </param>
/// <param name="isPublishedCopy">
/// Specifies whether to retrieve the published copy.
/// </param>
/// <returns>
/// The picture step.
/// </returns>
public PictureStepDto FetchPictureStep(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].[GetPictureStep] @FieldId = @fieldId;";
using (var connectionManager = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
using (var cmd = new SqlCommand(CommandText, connectionManager.Connection))
{
cmd.Parameters.AddWithValue("@processName", processName);
cmd.Parameters.AddWithValue("@fieldName", fieldName);
cmd.Parameters.AddWithValue("@isPublishedCopy", isPublishedCopy);
using (var reader = cmd.ExecuteReader())
{
using (var safeReader = new SafeDataReader(reader))
{
if (safeReader.Read())
{
var dto = new PictureStepDto
{
Id = safeReader.GetInt32(0),
SearchWidth = safeReader.GetInt32(1),
SearchHeight = safeReader.GetInt32(2),
DetailsWidth = safeReader.GetInt32(3),
DetailsHeight = safeReader.GetInt32(4),
UseOriginalSize = safeReader.GetBoolean(5)
};
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;
}
private static IEnumerable<DataIndexDbDefinition> GetTableIndexes(ITableDefinition table, string prefix)
{
const char EscapeCharacter = '!';
using (var ctx = GetRuntimeDatabaseConnectionManager())
{
var commandText = string.Format(CultureInfo.InvariantCulture, @"
SELECT
ind.name as IndexName
,t.name as ProcessName
,ind.filter_definition as FilterDefinition
,col.name as FieldName
,ic.is_included_column as IsIncluded
FROM
sys.indexes ind
INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN sys.tables t ON ind.object_id = t.object_id AND ind.name LIKE '{0}%' ESCAPE '{1}'
WHERE ind.object_id = (SELECT OBJECT_ID(@tableName))
ORDER BY ind.name, ic.key_ordinal", AdoHelper.EscapeLikePattern(prefix, EscapeCharacter), EscapeCharacter);
using (var command = new SqlCommand(commandText, ctx.Connection))
{
command.Parameters.AddWithValue("@tableName", table.Name);
using (var reader = new SafeDataReader(command.ExecuteReader()))
{
var indexList = new Collection<DataIndexDbDefinition>();
while (reader.Read())
{
var indexName = reader.GetString(0);
var processName = reader.GetString(1);
var index = indexList.FirstOrDefault(ix => ix.IndexName == indexName && ix.ProcessName == processName);
if (index == null)
{
index = new DataIndexDbDefinition { IndexName = indexName, ProcessName = processName, FilterDefinition = reader.GetString(2) };
indexList.Add(index);
}
var fieldName = reader.GetString(3);
var included = reader.GetBoolean(4);
index.IndexFields.Add(new DataIndexFieldDbDefinition(fieldName, !included));
}
return indexList;
}
}
}
}
/// <summary>
/// Retrieves published process with filters.
/// </summary>
/// <param name="publishedProcessId">The published process id.</param>
/// <returns>The <see cref="PublishedProcessWithFiltersInfoDto" />.</returns>
public PublishedProcessWithFiltersInfoDto FetchPublishedProcessWithFilters(int publishedProcessId)
{
var result = new PublishedProcessWithFiltersInfoDto();
const string CommandText =
@"
SELECT pp.Id
,Name
,p.Id AS ProcessId
,p.SystemName
,p.BaseProcessId
,p.ProcessOption
,p.IsStateEnabled
FROM [dbo].[PublishedProcesses] pp
INNER JOIN Processes p
ON pp.ProcessId = p.Id
WHERE pp.id = @id";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
using (var cmd = new SqlCommand(CommandText, ctx.Connection))
{
cmd.Parameters.AddWithValue("@id", publishedProcessId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
var processId = reader.GetInt32(2);
result = new PublishedProcessWithFiltersInfoDto(
FetchProcessFilters(processId),
FetchCrossRefFields(processId, Guid.Empty, true),
FetchReverseCrossRefFields(processId, Guid.Empty, true),
FetchProcessInfoStates(processId))
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
ProcessId = processId,
SystemName = reader.GetString(3),
IsStateEnabled = reader.GetBoolean(6)
};
if (!reader.IsDBNull(4))
{
result.BaseProcess = this.FetchPublishedProcessWithFilters(reader.GetInt32(4));
}
}
}
}
}
return result;
}
public Cart Map(SafeDataReader reader)
{
var item = (Cart)Activator.CreateInstance(typeof(Cart), true);
using (BypassPropertyChecks(item))
{
item.CartId = reader.GetInt32("CartId");
item.UniqueID = reader.GetInt32("UniqueID");
item.ItemId = reader.GetString("ItemId");
item.Name = reader.GetString("Name");
item.Type = reader.GetString("Type");
item.Price = reader.GetDecimal("Price");
item.CategoryId = reader.GetString("CategoryId");
item.ProductId = reader.GetString("ProductId");
item.IsShoppingCart = reader.GetBoolean("IsShoppingCart");
item.Quantity = reader.GetInt32("Quantity");
}
MarkOld(item);
MarkAsChild(item);
return item;
}
public PublishedProcessInfoDTO FetchPublishedProcess(int id)
{
var result = new PublishedProcessInfoDTO();
var sql = string.Format(CultureInfo.InvariantCulture, @"
SELECT pp.Id
,ISNULL(pl.ProcessName, p.[Name])
,p.Id AS ProcessId
,p.SystemName
,p.BaseProcessId
,p.ProcessOption
,p.SimpleProcess
,p.IsStateEnabled
FROM [dbo].[PublishedProcesses] pp
INNER JOIN Processes p ON pp.ProcessId = p.Id
INNER JOIN Processes p2 ON pp.ProcessGuid = p2.[Guid] and p2.[IsPublishedCopy] = 0
LEFT OUTER JOIN dbo.Localizations l ON l.CultureName = '{0}'
LEFT OUTER JOIN dbo.ProcessLocalizations pl ON pl.LocalizationId = l.Id AND p2.Id = pl.ProcessId
WHERE pp.id = @id", System.Threading.Thread.CurrentThread.CurrentUICulture.Name);
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(sql, cn))
{
cmd.Parameters.AddWithValue("@id", id);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
result = new PublishedProcessInfoDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
ProcessId = reader.GetInt32(2),
SystemName = reader.GetString(3),
ProcessOption =
!string.IsNullOrEmpty(reader.GetString(5))
? (ProcessOption)
Enum.Parse(
typeof(ProcessOption), reader.GetString(5))
: ProcessOption.None,
SimpleProcess = reader.GetBool(6),
IsStateEnabled = reader.GetBoolean(7)
};
if (!reader.IsDBNull(4))
{
result.BaseProcess = this.FetchPublishedProcess(reader.GetInt32(4));
}
}
}
}
}
return result;
}