private VoucherEditDto FetchVoucherEdit(SafeDataReader dr)
{
var voucherEdit = new VoucherEditDto();
// Value properties
voucherEdit.Id = dr.GetInt32("tran_id");
voucherEdit.Amount = (Decimal?)dr.GetValue("tran_amount");
voucherEdit.PersonId = !dr.IsDBNull("person_id") ? dr.GetString("person_id") : null;
voucherEdit.NamePrefix = !dr.IsDBNull("name_prefix") ? dr.GetString("name_prefix") : null;
voucherEdit.First = !dr.IsDBNull("first_name") ? dr.GetString("first_name") : null;
voucherEdit.Middle = !dr.IsDBNull("middle_name") ? dr.GetString("middle_name") : null;
voucherEdit.Last = !dr.IsDBNull("last_name") ? dr.GetString("last_name") : null;
voucherEdit.Suffix = !dr.IsDBNull("name_suffix") ? dr.GetString("name_suffix") : null;
voucherEdit.Title = !dr.IsDBNull("title") ? dr.GetString("title") : null;
voucherEdit.Company = !dr.IsDBNull("company") ? dr.GetString("company") : null;
voucherEdit.AddressLine1 = !dr.IsDBNull("address_1") ? dr.GetString("address_1") : null;
voucherEdit.AddressLine2 = !dr.IsDBNull("address_2") ? dr.GetString("address_2") : null;
voucherEdit.Municipality = !dr.IsDBNull("municipality") ? dr.GetString("municipality") : null;
voucherEdit.Region = !dr.IsDBNull("region") ? dr.GetString("region") : null;
voucherEdit.PostalCode = !dr.IsDBNull("postal_code") ? dr.GetString("postal_code") : null;
voucherEdit.Country = !dr.IsDBNull("country") ? dr.GetString("country") : null;
voucherEdit.PhoneNumber = !dr.IsDBNull("phone_number") ? dr.GetString("phone_number") : null;
voucherEdit.EmailAddress = !dr.IsDBNull("email") ? dr.GetString("email") : null;
voucherEdit.Updated = !dr.IsDBNull("updated") ? dr.GetSmartDate("updated", true) : null;
return voucherEdit;
}
private OpenBatchListItemDto Fetch(SafeDataReader dr)
{
var openBatchListItem = new OpenBatchListItemDto();
openBatchListItem.Num = dr.GetInt32("batch_num");
openBatchListItem.Label = !dr.IsDBNull("batch_label") ? dr.GetString("batch_label") : null;
return openBatchListItem;
}
private AccountDto Fetch(IDataReader data)
{
var account = new AccountDto();
using (var dr = new SafeDataReader(data))
{
if (dr.Read())
{
account.Id = dr.GetInt32("account_id");
account.Name = dr.GetString("account_name");
account.Description = !dr.IsDBNull("account_dscr") ? dr.GetString("account_dscr") : null;
account.Number = !dr.IsDBNull("account_number") ? dr.GetString("account_number") : null;
account.Balance = (Decimal?)dr.GetValue("balance");
account.LastReconciliationDate = !dr.IsDBNull("last_rec_date") ? dr.GetSmartDate("last_rec_date", true) : null;
account.LastReconciliationBalance = (Decimal?)dr.GetValue("last_rec_balance");
}
}
return account;
}
private BatchEditDto Fetch(IDataReader data)
{
var batchEdit = new BatchEditDto();
using (var dr = new SafeDataReader(data))
{
if (dr.Read())
{
batchEdit.Num = dr.GetInt32("batch_num");
batchEdit.Date = !dr.IsDBNull("batch_date") ? dr.GetSmartDate("batch_date", true) : null;
batchEdit.PayDate = !dr.IsDBNull("pay_date") ? dr.GetSmartDate("pay_date", true) : null;
batchEdit.Amount = (Decimal?)dr.GetValue("batch_amount");
batchEdit.JobNum = (int?)dr.GetValue("job_num");
batchEdit.Description = !dr.IsDBNull("batch_dscr") ? dr.GetString("batch_dscr") : null;
batchEdit.Updated = !dr.IsDBNull("updated") ? dr.GetSmartDate("updated", true) : null;
batchEdit.ThankYou1 = !dr.IsDBNull("thank_you_1") ? dr.GetString("thank_you_1") : null;
batchEdit.StudyTopic = !dr.IsDBNull("study_topic") ? dr.GetString("study_topic") : null;
batchEdit.ThankYou2 = !dr.IsDBNull("thank_you_2") ? dr.GetString("thank_you_2") : null;
batchEdit.MarketingResearchMessage = !dr.IsDBNull("marketing_research_message") ? dr.GetString("marketing_research_message") : null;
}
FetchChildren(dr);
}
return batchEdit;
}
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>
/// 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 Item Map(SafeDataReader reader)
{
var item = (Item)Activator.CreateInstance(typeof(Item), true);
using (BypassPropertyChecks(item))
{
item.ItemId = reader.GetString("ItemId");
item.OriginalItemId = reader.GetString("ItemId");
item.ProductId = reader.GetString("ProductId");
item.ListPrice = reader.IsDBNull("ListPrice") ? (System.Decimal?)null : reader.GetDecimal("ListPrice");
item.UnitCost = reader.IsDBNull("UnitCost") ? (System.Decimal?)null : reader.GetDecimal("UnitCost");
item.Supplier = reader.IsDBNull("Supplier") ? (System.Int32?)null : reader.GetInt32("Supplier");
item.Status = reader.GetString("Status");
item.Name = reader.GetString("Name");
item.Image = reader.GetString("Image");
}
MarkOld(item);
return item;
}
/// <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>
/// Retrieves published process by process id.
/// </summary>
/// <param name="processId">The process id.</param>
/// <returns>The <see cref="PublishedProcessInfoDTO" />.</returns>
public PublishedProcessInfoDTO FetchPublishedProcessByProcessId(int processId)
{
var result = new PublishedProcessInfoDTO();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
const string Sql = @"
SELECT pp.Id
,Name
,p.Id AS ProcessId
,p.SystemName
,p.BaseProcessId
FROM [dbo].[PublishedProcesses] pp
INNER JOIN Processes p
ON pp.ProcessId = p.Id
WHERE p.id = @processId";
using (var cmd = new SqlCommand(Sql, cn))
{
cmd.Parameters.AddWithValue("@processId", processId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
result = new PublishedProcessInfoDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
ProcessId = reader.GetInt32(2),
SystemName = reader.GetString(3)
};
if (!reader.IsDBNull(4))
{
result.BaseProcess = this.FetchPublishedProcess(reader.GetInt32(4));
}
}
}
}
}
return result;
}
public VoucherEditDto Fetch(int id)
{
var voucherEdit = new VoucherEditDto();
using (var ctx = ConnectionManager<SqlConnection>.GetManager("CoopCheck"))
{
using (var cmd = new SqlCommand("dbo.dsa_GetVoucher", ctx.Connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@tran_id", id).DbType = DbType.Int32;
var dr = new SafeDataReader(cmd.ExecuteReader());
dr.Read();
voucherEdit.Id = dr.GetInt32("tran_id");
voucherEdit.Amount = (Decimal?)dr.GetValue("tran_amount");
voucherEdit.PersonId = !dr.IsDBNull("person_id") ? dr.GetString("person_id") : null;
voucherEdit.NamePrefix = !dr.IsDBNull("name_prefix") ? dr.GetString("name_prefix") : null;
voucherEdit.First = !dr.IsDBNull("first_name") ? dr.GetString("first_name") : null;
voucherEdit.Middle = !dr.IsDBNull("middle_name") ? dr.GetString("middle_name") : null;
voucherEdit.Last = !dr.IsDBNull("last_name") ? dr.GetString("last_name") : null;
voucherEdit.Suffix = !dr.IsDBNull("name_suffix") ? dr.GetString("name_suffix") : null;
voucherEdit.Title = !dr.IsDBNull("title") ? dr.GetString("title") : null;
voucherEdit.Company = !dr.IsDBNull("company") ? dr.GetString("company") : null;
voucherEdit.AddressLine1 = !dr.IsDBNull("address_1") ? dr.GetString("address_1") : null;
voucherEdit.AddressLine2 = !dr.IsDBNull("address_2") ? dr.GetString("address_2") : null;
voucherEdit.Municipality = !dr.IsDBNull("municipality") ? dr.GetString("municipality") : null;
voucherEdit.Region = !dr.IsDBNull("region") ? dr.GetString("region") : null;
voucherEdit.PostalCode = !dr.IsDBNull("postal_code") ? dr.GetString("postal_code") : null;
voucherEdit.Country = !dr.IsDBNull("country") ? dr.GetString("country") : null;
voucherEdit.PhoneNumber = !dr.IsDBNull("phone_number") ? dr.GetString("phone_number") : null;
voucherEdit.EmailAddress = !dr.IsDBNull("email") ? dr.GetString("email") : null;
voucherEdit.Updated = !dr.IsDBNull("updated") ? dr.GetSmartDate("updated", true) : null;
}
}
return voucherEdit;
}
public PublishedProcessInfoDTO FetchPublishedProcess(int id)
{
var result = new PublishedProcessInfoDTO();
var sql = string.Format(CultureInfo.InvariantCulture, @"
SELECT pp.Id
,ISNULL(pl.ProcessName, p.[Name])
,p.Id AS ProcessId
,p.SystemName
,p.BaseProcessId
,p.ProcessOption
,p.SimpleProcess
,p.IsStateEnabled
FROM [dbo].[PublishedProcesses] pp
INNER JOIN Processes p ON pp.ProcessId = p.Id
INNER JOIN Processes p2 ON pp.ProcessGuid = p2.[Guid] and p2.[IsPublishedCopy] = 0
LEFT OUTER JOIN dbo.Localizations l ON l.CultureName = '{0}'
LEFT OUTER JOIN dbo.ProcessLocalizations pl ON pl.LocalizationId = l.Id AND p2.Id = pl.ProcessId
WHERE pp.id = @id", System.Threading.Thread.CurrentThread.CurrentUICulture.Name);
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(sql, cn))
{
cmd.Parameters.AddWithValue("@id", id);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
result = new PublishedProcessInfoDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
ProcessId = reader.GetInt32(2),
SystemName = reader.GetString(3),
ProcessOption =
!string.IsNullOrEmpty(reader.GetString(5))
? (ProcessOption)
Enum.Parse(
typeof(ProcessOption), reader.GetString(5))
: ProcessOption.None,
SimpleProcess = reader.GetBool(6),
IsStateEnabled = reader.GetBoolean(7)
};
if (!reader.IsDBNull(4))
{
result.BaseProcess = this.FetchPublishedProcess(reader.GetInt32(4));
}
}
}
}
}
return result;
}
/// <summary>
/// Retrieves 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;
}
/// <summary>
/// Retrieves synchronized processes.
/// </summary>
/// <returns>The <see cref="IList" />.</returns>
public IEnumerable<PublishedProcessInfoDTO> FetchSynchronizedProcesses()
{
var result = new List<PublishedProcessInfoDTO>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
const string CommandText = @"
SELECT pp.Id
,p.[Name]
,p.[SystemName]
,p.Id AS ProcessId
,p.[BaseProcessId]
,p.[IconId]
FROM [dbo].[PublishedProcesses] pp
INNER JOIN Processes p
ON pp.ProcessId = p.Id
WHERE p.IsRemoved = 0 AND p.IsInactive = 0 AND EXISTS(
SELECT *
FROM [dbo].[SyncProcesses] sp
WHERE sp.ProcessId = p.Id)
ORDER BY
p.Name
";
using (var cmd = new SqlCommand(CommandText, connection))
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var dto = new PublishedProcessInfoDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
SystemName = reader.GetString(2),
ProcessId = reader.GetInt32(3),
IconId = reader.GetNullableInt(5)
};
if (!reader.IsDBNull(4))
{
dto.BaseProcess = this.FetchPublishedProcess(reader.GetInt(4));
}
result.Add(dto);
}
}
}
return result;
}
public IList<PublishedProcessInfoDTO> FetchPublishedProcesses(string culture)
{
var result = new List<PublishedProcessInfoDTO>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
var commandText = string.Format(CultureInfo.InvariantCulture, @"
SELECT pp.Id
,ISNULL(pl.ProcessName, p.[Name])
,p.[SystemName]
,p.Id AS ProcessId
,p.[BaseProcessId]
,p2.[IconId]
,p.[Guid]
,p.[ProcessOption]
,p.[IsStateEnabled]
FROM [dbo].[PublishedProcesses] pp
INNER JOIN Processes p ON pp.ProcessId = p.Id
INNER JOIN Processes p2 ON pp.ProcessGuid = p2.[Guid] and p2.[IsPublishedCopy] = 0
LEFT OUTER JOIN dbo.ProcessLocalizations pl
INNER JOIN dbo.Localizations l ON pl.LocalizationId = l.Id AND l.CultureName = '{0}'
ON p2.Id = pl.ProcessId
WHERE p.IsRemoved = 0 AND p2.IsRemoved = 0
ORDER BY p.Name", culture);
using (var cmd = new SqlCommand(commandText, connection))
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var dto = new PublishedProcessInfoDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
SystemName = reader.GetString(2),
ProcessId = reader.GetInt32(3),
IconId = reader.GetNullableInt(5),
ProcessGuid = reader.GetGuid(6),
ProcessOption = reader.GetEnum(7, ProcessOption.None),
IsStateEnabled = reader.GetBoolean(8)
};
if (!reader.IsDBNull(4))
{
dto.BaseProcess = this.FetchPublishedProcess(reader.GetInt(4));
}
result.Add(dto);
}
}
}
return result;
}
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>
/// 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>
/// Gets the integration service scheduled calls.
/// </summary>
/// <returns>IEnumerable{IntegrationServiceScheduledCallDto}.</returns>
public IEnumerable<IntegrationServiceScheduledCallDto> GetIntegrationServiceScheduledCalls()
{
const string CommandText = @"
SELECT
sc.[Id]
,sc.[CreationDate]
,sc.[IntegrationServiceGuid]
,sc.[ProcessName]
,sc.[ItemId]
,sc.[CallCount]
,sc.[Status]
,wm.[ServiceDescriptionId]
,wm.[ContractTypeName]
,wm.[MethodName]
,wm.[Data]
,wm.[Options]
FROM
[dbo].[IntegrationServiceScheduledCalls] sc
INNER JOIN [dbo].[IntegrationServiceWebMethodScheduledCalls] wm ON wm.[ScheduledCallId] = sc.[Id]
WHERE sc.[Status] = 'Scheduled';
SELECT
sc.[Id]
,sc.[CreationDate]
,sc.[IntegrationServiceGuid]
,sc.[ProcessName]
,sc.[ItemId]
,sc.[CallCount]
,sc.[Status]
,url.[Url]
,url.[Data]
,url.[HttpMethod]
FROM
[dbo].[IntegrationServiceScheduledCalls] sc
INNER JOIN [dbo].[IntegrationServiceUrlScheduledCalls] url ON url.[ScheduledCallId] = sc.[Id]
WHERE sc.[Status] = 'Scheduled';";
var result = new List<IntegrationServiceScheduledCallDto>();
// Don't enlist this connection in current TransationScope.
// When this method is called from dynamic assemblies, it will fail if MS DTC is not enabled.
var csb = new SqlConnectionStringBuilder(Database.VeyronMeta) { Enlist = false };
using (var cn = new SqlConnection(csb.ConnectionString))
{
cn.Open();
using (var cmd = new SqlCommand(CommandText, cn))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var dto = new IntegrationServiceWebMethodScheduledCallDto
{
Id = reader.GetInt32(0),
CreationDate = reader.GetDateTime(1),
IntegrationServiceGuid = reader.GetGuid(2),
ProcessName = reader.GetString(3),
ItemId = reader.GetInt32(4),
CallCount = reader.GetInt32(5),
Status =
(ServiceCallStatus)
Enum.Parse(
typeof(ServiceCallStatus), reader.GetString(6), true),
ServiceDescriptionId = reader.GetInt32(7),
ContractTypeName = reader.GetString(8),
MethodName = reader.GetString(9),
Data = reader.GetString(10),
Options = !reader.IsDBNull(11) ? reader.GetString(11) : null
};
result.Add(dto);
}
reader.NextResult();
while (reader.Read())
{
var dto = new IntegrationServiceUrlScheduledCallDto
{
Id = reader.GetInt32(0),
CreationDate = reader.GetDateTime(1),
IntegrationServiceGuid = reader.GetGuid(2),
ProcessName = reader.GetString(3),
ItemId = reader.GetInt32(4),
CallCount = reader.GetInt32(5),
Status =
(ServiceCallStatus)
Enum.Parse(typeof(ServiceCallStatus), reader.GetString(6), true),
Url = reader.GetString(7),
Data = reader.GetString(8),
HttpMethod =
(UrlServiceCallMethod)
Enum.Parse(
typeof(UrlServiceCallMethod), reader.GetString(9), true)
};
result.Add(dto);
}
}
}
}
return result;
}