/// <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;
}
public static ArrayList GetAmazingChartSchedule(string _beginDate, string _endDate)
{
ArrayList items = new ArrayList();
using (SqlConnection cn = new SqlConnection(Database.AmazingChartsConnection))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "SELECT VisitID,Date,PatientID,Name,Phone,VisitType,"
+ "Comments,Booker,DateBooked,ProviderID,Duration,XLinkProviderID,"
+ "VisitIdExternal,DateLastTouched,LastTouchedBy,DateRowAdded"
+ " FROM Scheduling"
+ " where Date between '" + _beginDate + "'" + " and '" + _endDate + "'"
+ " order by Date;";
using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
items.Add(dr.GetInt32(0).ToString() + "~"
+ dr.GetDateTime(1).ToShortDateString() + "~"
+ dr.GetDateTime(1).ToShortTimeString() + "~"
+ dr.GetInt32(2).ToString() + "~"
+ dr.GetString(3) + "~"
+ dr.GetString(4) + "~"
+ dr.GetString(5) + "~"
+ dr.GetString(6) + "~"
+ dr.GetString(7) + "~"
+ dr.GetDateTime(8).ToShortDateString() + "~"
+ dr.GetInt32(9).ToString() + "~");
}
return items;
}
}
}
}
public static SortingProcessList GetSortingProcessList(string sortingdate, string taskno, string picklinecode)
{
SortingProcessList sortingProcessList = new SortingProcessList();
using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString))
{
cn.Open();
using (var cm1 = cn.CreateCommand())
{
cm1.CommandText =
"SELECT * FROM t_sortline_process WHERE " +
"(1 = 1 and ((@SORTINGTASKNO is null) or (SORTINGTASKNO = @SORTINGTASKNO))) AND" +
"(1 = 1 and ((@SORT_DATE is null) or (ORDERDATE = @SORT_DATE))) AND" +
"(1 = 1 and ((@SORTLINE_CODE is null) or (PICKLINECODE = @SORTLINE_CODE)))";
cm1.Parameters.AddWithValue("@SORTINGTASKNO", taskno);
cm1.Parameters.AddWithValue("@SORT_DATE", sortingdate);
cm1.Parameters.AddWithValue("@SORTLINE_CODE", picklinecode);
using (var dr = new Csla.Data.SafeDataReader(cm1.ExecuteReader()))
{
while (dr.Read())
{
SortingProcessInfo sortingProcess = new SortingProcessInfo();
sortingProcess.SORTINGTASKNO = dr.GetString("SORTINGTASKNO");
sortingProcess.ORDERDATE = dr.GetString("ORDERDATE");
sortingProcess.PICKLINECODE = dr.GetString("PICKLINECODE");
sortingProcess.PICKLINENAME = dr.GetString("PICKLINENAME");
sortingProcess.QTY_PRODCUT_TOT = dr.GetInt32("QTY_PRODCUT_TOT");
sortingProcess.QTY_ROUTE_TOT = dr.GetInt32("QTY_ROUTE_TOT");
sortingProcess.QTY_CUSTOMER_TOT = dr.GetInt32("QTY_CUSTOMER_TOT");
sortingProcess.QTY_PRODUCT = dr.GetInt32("QTY_PRODUCT");
sortingProcess.QTY_ROUTE = dr.GetInt32("QTY_ROUTE");
sortingProcess.QTY_CUSTOMER = dr.GetInt32("QTY_CUSTOMER");
sortingProcess.CUSTOMER_CODE = dr.GetString("CUSTOMER_CODE");
sortingProcess.CUSTOMER_DESC = dr.GetString("CUSTOMER_DESC");
sortingProcess.ROUTE_CODE = dr.GetString("ROUTE_CODE");
sortingProcess.ROUTE_NAME = dr.GetString("ROUTE_NAME");
sortingProcess.RECEIVE_TIME = dr.GetDateTime("RECEIVE_TIME");
sortingProcess.EFFICIENCY = dr.GetDouble("EFFICIENCY");
sortingProcessList.Add(sortingProcess);
}
}
}
}
return(sortingProcessList);
}
public static Dictionary <string, string> GetSortingProcessInfo(string sortingdate, string taskno, string picklinecode)
{
Dictionary <string, string> sortingDictionary = new Dictionary <string, string>();
using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString))
{
cn.Open();
using (var cm1 = cn.CreateCommand())
{
cm1.CommandText =
"SELECT * FROM t_sortline_process WHERE " +
"(1 = 1 and ((@SORTINGTASKNO is null) or (SORTINGTASKNO = @SORTINGTASKNO))) AND" +
"(1 = 1 and ((@SORT_DATE is null) or (ORDERDATE = @SORT_DATE))) AND" +
"(1 = 1 and ((@SORTLINE_CODE is null) or (PICKLINECODE = @SORTLINE_CODE)))";
cm1.Parameters.AddWithValue("@SORTINGTASKNO", taskno);
cm1.Parameters.AddWithValue("@SORT_DATE", sortingdate);
cm1.Parameters.AddWithValue("@SORTLINE_CODE", picklinecode);
using (var dr = new Csla.Data.SafeDataReader(cm1.ExecuteReader()))
{
if (dr.Read())
{
sortingDictionary.Add("SORTINGTASKNO", dr.GetString("SORTINGTASKNO"));
sortingDictionary.Add("ORDERDATE ", dr.GetString("ORDERDATE"));
sortingDictionary.Add("PICKLINECODE", dr.GetString("PICKLINECODE"));
sortingDictionary.Add("PICKLINENAME", dr.GetString("PICKLINENAME"));
sortingDictionary.Add("QTY_PRODCUT_TOT", dr.GetInt32("QTY_PRODCUT_TOT").ToString());
sortingDictionary.Add("QTY_ROUTE_TOT", dr.GetInt32("QTY_ROUTE_TOT").ToString());
sortingDictionary.Add("QTY_CUSTOMER_TOT", dr.GetInt32("QTY_CUSTOMER_TOT").ToString());
sortingDictionary.Add("QTY_PRODUCT", dr.GetInt32("QTY_PRODUCT").ToString());
sortingDictionary.Add("QTY_ROUTE", dr.GetInt32("QTY_ROUTE").ToString());
sortingDictionary.Add("QTY_CUSTOMER", dr.GetInt32("QTY_CUSTOMER").ToString());
sortingDictionary.Add("CUSTOMER_CODE", dr.GetString("CUSTOMER_CODE") != "" ? dr.GetString("CUSTOMER_CODE"):"无");
sortingDictionary.Add("CUSTOMER_DESC", dr.GetString("CUSTOMER_DESC") != "" ? dr.GetString("CUSTOMER_DESC"):"无");
sortingDictionary.Add("ROUTE_CODE", dr.GetString("ROUTE_CODE") != "" ? dr.GetString("ROUTE_CODE") : "无");
sortingDictionary.Add("ROUTE_NAME", dr.GetString("ROUTE_NAME") != "" ? dr.GetString("ROUTE_NAME") : "无");
sortingDictionary.Add("RECEIVE_TIME", dr.GetDateTime("RECEIVE_TIME").ToString());
sortingDictionary.Add("EFFICIENCY", dr.GetDouble("EFFICIENCY").ToString("#0.00") + "条/小时");
sortingDictionary.Add("Progress", dr.GetDouble("Progress").ToString());
}
}
}
}
return(sortingDictionary);
}
/// <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;
}
}
}
}
public OrderStatus Map(SafeDataReader reader)
{
var item = (OrderStatus)Activator.CreateInstance(typeof(OrderStatus), true);
using (BypassPropertyChecks(item))
{
item.OrderId = reader.GetInt32("OrderId");
item.OriginalOrderId = reader.GetInt32("OrderId");
item.LineNum = reader.GetInt32("LineNum");
item.OriginalLineNum = reader.GetInt32("LineNum");
item.Timestamp = reader.GetDateTime("Timestamp");
item.Status = reader.GetString("Status");
}
MarkOld(item);
return item;
}
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;
}
public Order Map(SafeDataReader reader)
{
var item = (Order)Activator.CreateInstance(typeof(Order), true);
using (BypassPropertyChecks(item))
{
item.OrderId = reader.GetInt32("OrderId");
item.UserId = reader.GetString("UserId");
item.OrderDate = reader.GetDateTime("OrderDate");
item.ShipAddr1 = reader.GetString("ShipAddr1");
item.ShipAddr2 = reader.GetString("ShipAddr2");
item.ShipCity = reader.GetString("ShipCity");
item.ShipState = reader.GetString("ShipState");
item.ShipZip = reader.GetString("ShipZip");
item.ShipCountry = reader.GetString("ShipCountry");
item.BillAddr1 = reader.GetString("BillAddr1");
item.BillAddr2 = reader.GetString("BillAddr2");
item.BillCity = reader.GetString("BillCity");
item.BillState = reader.GetString("BillState");
item.BillZip = reader.GetString("BillZip");
item.BillCountry = reader.GetString("BillCountry");
item.Courier = reader.GetString("Courier");
item.TotalPrice = reader.GetDecimal("TotalPrice");
item.BillToFirstName = reader.GetString("BillToFirstName");
item.BillToLastName = reader.GetString("BillToLastName");
item.ShipToFirstName = reader.GetString("ShipToFirstName");
item.ShipToLastName = reader.GetString("ShipToLastName");
item.AuthorizationNumber = reader.GetInt32("AuthorizationNumber");
item.Locale = reader.GetString("Locale");
}
MarkOld(item);
return item;
}
/// <summary>
/// Reads metrics.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private static void ReadMetrics(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
while (sr.Read())
{
process.Metrics.Add(new ProcessMetricEditDto
{
Id = sr.GetInt32(0),
ProcessId = sr.GetInt32(1),
LastModifiedOn = sr.GetDateTime(2),
GuidId = sr.GetString(3),
Name = sr.GetString(4),
Documentation = sr.GetString(5),
SummaryType = sr.GetString(6),
LockFilter = sr.GetBool(7),
SnapshotFrequency = sr.GetString(8),
MetricFieldSystemName = sr.GetString(9),
GroupFieldOneSystemName = sr.GetString(10),
GroupFieldTwoSystemName = sr.GetString(11),
GroupFieldThreeSystemName = sr.GetString(12),
GroupFieldFourSystemName = sr.GetString(13),
FilterGuid = sr.GetGuid(14),
OrderByMetricField = sr.GetString(15),
OrderByAscending = (bool?)sr.GetValue(16),
FilterDefinition = sr.GetString(17)
});
}
}
/// <summary>
/// Reads filters.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private static void ReadFilters(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
while (sr.Read())
{
process.Filters.Add(
new ProcessFilterEditDto
{
Id = sr.GetInt32(0),
ProcessId = sr.GetInt32(1),
LastModifiedOn = sr.GetDateTime(2),
GuidId = sr.GetString(3),
Name = sr.GetString(4),
Documentation = sr.GetString(5),
FilterDefinition = sr.GetString(6),
RoleIds = sr.GetString(7)
});
}
}
/// <summary>
/// Reads commands.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private static void ReadCommands(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
while (sr.Read())
{
var commandDto = new ProcessCommandEditDto
{
Id = sr.GetInt32(0),
ProcessId = sr.GetInt32(1),
GuidId = sr.GetGuid(2).ToString(),
LastModifiedOn = sr.GetDateTime(3),
CommandName = sr.GetString(4),
CommandType = sr.GetString(5),
Documentation = sr.GetString(6),
IntegrationServiceGuid = sr.GetNullableGuid(7),
DataTriggerGuid = sr.GetNullableGuid("DataTriggerGuid")
};
process.Commands.Add(commandDto);
}
ReadCommandSecurityConfiguration(process, sr);
}
/// <summary>
/// Reads process views.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private void ReadProcessViews(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
while (sr.Read())
{
process.ViewList.Add(new ProcessViewEditDto
{
Id = sr.GetInt32(0),
ProcessId = sr.GetInt32(1),
LastModifiedOn = sr.GetDateTime(2),
Guid = sr.GetGuid(3),
Name = sr.GetString(4),
Documentation = sr.GetString(5),
ViewType = sr.GetString(6),
ViewConfiguration = sr.GetString(7)
});
}
ReadProcessViewSections(process, sr);
}
public RevisionHistoryDto FetchRevisionHistory(int id)
{
RevisionHistoryDto result = null;
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
const string commandText = @"
SELECT
[Id]
,[Date]
,[ProcessId]
,[Body]
,[ModifiedBy]
FROM [dbo].[ProcessRevisionHistory]
WHERE [id] = @p_id;
";
using (var cmd = new SqlCommand(commandText, connection))
{
cmd.Parameters.AddWithValue("@p_id", id);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
result = new RevisionHistoryDto
{
Id = id,
Date = reader.GetDateTime(1),
ProcessId = reader.GetInt32(2),
Body = reader.GetString(3),
ModifiedBy = reader.GetString(4)
};
}
}
}
}
return result;
}
public IEnumerable<RevisionHistoryDto> FetchRevisionHistoryList(int processId)
{
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
const string commandText = @"
SELECT
[Id]
,[Date]
,[ProcessId]
,[ModifiedBy]
FROM [dbo].[ProcessRevisionHistory]
WHERE [ProcessId] = @processId
ORDER BY Date DESC
";
var result = new List<RevisionHistoryDto>();
using (var cmd = new SqlCommand(commandText, connection))
{
cmd.Parameters.AddWithValue("@processId", processId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var revisionHistoryInfo = new RevisionHistoryDto
{
Id = reader.GetInt32(0),
Date = reader.GetDateTime(1),
ProcessId = reader.GetInt32(2),
ModifiedBy = reader.GetString(3)
};
result.Add(revisionHistoryInfo);
}
}
}
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;
}
/// <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;
}
public static StringCollection PatientListSelectionString(string _pattern)
{
_pattern = _pattern.Replace("'", "''");
StringCollection items = new StringCollection();
using (SqlConnection cn = new SqlConnection(Database.WaldenConnect))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "select Display,DateOfBirth,"
+ " Address1,City,PatientID"
+ " from patients"
+ " where Display like '" + _pattern + "%'"
+ " and AccountID =" + Common.AccountID
+ " order by Display";
using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
items.Add(dr.GetString(0) + "~"
+ dr.GetDateTime(1).ToShortDateString() + "~"
+ dr.GetString(2) + "~"
+ dr.GetString(3) + "~"
+ dr.GetInt32(4).ToString());
}
return items;
}
}
}
}
/// <summary>
/// The read KPIs.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private static void ReadKpis(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
while (sr.Read())
{
process.Kpis.Add(new ProcessKpiEditDto
{
Id = sr.GetInt32(0),
ProcessId = sr.GetInt32(1),
MetricGuid = sr.GetString(2),
LastModifiedOn = sr.GetDateTime(3),
GuidId = sr.GetString(4),
Name = sr.GetString(5),
TargetValue = sr.GetDouble(6),
FavorableDirection = sr.GetString(7),
GreenIconUrl = sr.GetString(8),
GreenIconId = sr.GetInt32(9, null),
GreenValue = sr.GetDouble(10),
YellowIconUrl = sr.GetString(11),
YellowIconId = sr.GetInt32(12, null),
YellowValue = sr.GetDouble(13),
RedIconUrl = sr.GetString(14),
RedIconId = sr.GetInt32(15, null),
FilterGuid = sr.GetString(16, null),
FilterDefinition = sr.GetString(17, null),
Documentation = sr.GetString(18),
});
}
}
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 process filters.
/// </summary>
/// <param name="processId">The process id.</param>
/// <returns>The <see cref="IList{T}" />.</returns>
private static IList<ProcessFilterEditDto> FetchProcessFilters(int processId)
{
const string commandText = @"
SELECT [Id]
,[GuidId]
,[LastModifiedOn]
,[Name]
,[FilterDefinition]
FROM [dbo].[Filters]
WHERE [ProcessId] = @processId";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var connection = ctx.Connection;
var list = new List<ProcessFilterEditDto>();
using (var cmd = new SqlCommand(commandText, connection))
{
cmd.Parameters.AddWithValue("@processId", processId);
using (var r = new SafeDataReader(cmd.ExecuteReader()))
{
while (r.Read())
{
var filterInfo = new ProcessFilterEditDto
{
Id = r.GetInt(0),
GuidId = r.GetString(1),
LastModifiedOn = r.GetDateTime(2),
Name = r.GetString(3),
FilterDefinition = r.GetString(4)
};
list.Add(filterInfo);
}
}
return list;
}
}
}