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;
}
public IEnumerable<EDCEditDto> FetchEdcList()
{
const string sql = @"
SELECT [Id]
,[Name]
,[SystemName]
,[Documentation]
,[GuidId]
FROM [dbo].[ExternalDataConfiguration] edc
";
var result = new List<EDCEditDto>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (var cmd = new SqlCommand(sql, cn))
{
using (var sr = new SafeDataReader(cmd.ExecuteReader()))
{
while (sr.Read())
{
var parameter = new EDCEditDto
{
Id = sr.GetInt32(0),
Name = sr.GetString(1),
SystemName = sr.GetString(2),
Documentation = sr.GetString(3),
Guid = sr.GetGuid(4)
};
result.Add(parameter);
}
}
foreach (var dto in result)
{
dto.ConnectionList = new List<EDCConnectionEditDto>();
dto.DataVariableList = new List<EDCDataVariablesEditDto>();
FetchEDCConnectionList(cn, dto);
FetchDataVariableList(cn, dto);
}
}
}
return result;
}
/// <summary>
/// Fetch System Parameters
/// </summary>
/// <returns>IEnumerable{SystemParametersDto}.</returns>
/// <exception cref="DataAccessException"></exception>
public IEnumerable<SystemParametersDto> FetchSystemParameters()
{
const string Sql = @"
SELECT [Id]
,[Name]
,[Documentation]
,[Type]
,[ProcessSystemName]
,[Expression]
,[GuidId]
,[LastModifiedOn]
FROM [dbo].[SystemParameters] sp
WHERE sp.IsRemoved = 0
";
var result = new List<SystemParametersDto>();
Database.GetDataReader(
Sql,
reader =>
{
if (reader == null)
{
throw new DataAccessException(Resources.FailedToRetrieveSystemOptions);
}
using (var sr = new SafeDataReader(reader))
{
while (reader.Read())
{
var parameter = new SystemParametersDto
{
ParameterId = sr.GetInt32(0),
Name = sr.GetString(1),
Documentation = sr.GetString(2),
ParameterType = !string.IsNullOrEmpty(reader.GetString(3))
? (SystemParameterType)Enum.Parse(typeof(SystemParameterType), reader.GetString(3))
: SystemParameterType.Int,
PersonProcessSystemName = sr.GetString(4),
Expression = sr.GetString(5),
Guid = sr.GetGuid(6),
LastModifiedOn = sr.GetDateTime(7)
};
result.Add(parameter);
}
}
});
return result;
}
public static ArrayList GetTemplates(string _templateType)
{
ArrayList aTemplates = new ArrayList();
using (SqlConnection cn = new SqlConnection(Database.WaldenConnect))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "select B.Name ,B.TemplateID from TemplateType A,Templates B"
+ " where A.Name = '" + _templateType + "'"
+ " and A.AccountID = " + Common.AccountID
+ " and B.AccountID = " + Common.AccountID
+ " and A.TemplateTypeID = B.TemplateTypeID"
+ " order by B.Name";
Common.Log(cm.CommandText);
using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
aTemplates.Add(dr.GetString(0) + "~"
+ dr.GetInt32(1).ToString());
// + dr.GetDateTime(1).ToShortDateString() + "~"
// + dr.GetString(2) + "~"
// + dr.GetString(3) + "~"
// + dr.GetInt32(4).ToString());
}
return aTemplates;
}
}
}
}
public string GetDatabaseVersion()
{
var mySQLString = "Select mdbVersion From tblCaptions";
if (GetConnection(out _myAccessConn)) return null;
try
{
using (var myAccessCommand = new OleDbCommand(mySQLString, _myAccessConn))
{
_myAccessConn.Open();
using (var dr = new SafeDataReader(myAccessCommand.ExecuteReader()))
using (dr)
while (dr.Read())
{
_mdbVersion = dr.GetString(dr.GetOrdinal("mdbVersion"));
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
return null;
}
finally
{
_myAccessConn.Close();
}
return _mdbVersion;
}
/// <summary>
/// Retrieves action groups.
/// </summary>
/// <returns>The list of Action Group DTO objects.</returns>
public IEnumerable<ActionGroupDto> FetchActionGroups()
{
var result = new List<ActionGroupDto>();
const string Sql = @"
SELECT DISTINCT
[GroupName]
FROM
[dbo].[ActionDefinitions]
";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
using (var cmd = new SqlCommand(Sql, cn))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var dto = new ActionGroupDto { Name = reader.GetString(0) };
result.Add(dto);
}
}
}
}
return result;
}
/// <summary>
/// Gets the user dictionary words.
/// </summary>
/// <returns>
/// The collection of words.
/// </returns>
public IList<string> GetUserWords()
{
const string CommandText = @"
SELECT [Word]
FROM
[dbo].[SpellCheckerUserWords]";
using (var connectionManager = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
using (var cmd = new SqlCommand(CommandText, connectionManager.Connection))
{
using (var reader = cmd.ExecuteReader())
{
using (var safeReader = new SafeDataReader(reader))
{
var words = new List<string>();
while (safeReader.Read())
{
words.Add(safeReader.GetString("Word"));
}
return words;
}
}
}
}
}
public static StringCollection GetStringCollectionOfPatientChart(string _id)
{
StringCollection items = new StringCollection();
using (SqlConnection cn = new SqlConnection(Database.WaldenConnect))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "select ScanPath"
+ " from ScanPatientDocuments"
+ " where PatientID = " + _id
+ " and AccountID =" + Common.AccountID
+ " order by TabId,DateOfService";
using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
items.Add(Common.StoreComputer
+ Common.StoreShare + dr.GetString(0));
}
return items;
}
}
}
}
private AccountListItemDto Fetch(SafeDataReader dr)
{
var accountListItem = new AccountListItemDto();
accountListItem.Id = dr.GetInt32("account_id");
accountListItem.Name = dr.GetString("account_name");
return accountListItem;
}
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;
}
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;
}
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 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;
}
}
}
}
/// <summary>
/// Reads the palette.
/// </summary>
/// <param name="reader">The reader.</param>
/// <returns>PaletteDTO.</returns>
private static PaletteDTO ReadPalette(SafeDataReader reader)
{
return new PaletteDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
MainBackColor = (uint)reader.GetInt64(2),
MainFrontColor = (uint)reader.GetInt64(3),
StrongBackColor = (uint)reader.GetInt64(4),
StrongFrontColor = (uint)reader.GetInt64(5),
CalmBackColor = (uint)reader.GetInt64(6),
CalmFrontColor = (uint)reader.GetInt64(7),
};
}
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>
/// Reads search display fields.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private static void ReadSearchDisplayFields(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
while (sr.Read())
{
process.Search.Add(
new ProcessSearchEditDto
{
Id = sr.GetInt32(0),
ProcessId = sr.GetInt32(1),
GuidId = sr.GetGuid(2),
FieldSystemName = sr.GetString(3),
Position = sr.GetInt32(4),
Width = sr.GetInt32(5),
ShowInList = sr.GetBoolean(6)
});
}
}
/// <summary>
/// Fetches the navigation items.
/// </summary>
/// <param name="groupId">The group identifier.</param>
/// <returns>IEnumerable{NavigationItemDTO}.</returns>
public IEnumerable<NavigationItemDTO> FetchNavigationItems(int? groupId = null)
{
const string Sql = @"
SELECT ni.[Id] ,
p.[Name] ,
ni.NavigationGroupId
FROM NavigationItems ni
INNER JOIN PublishedProcesses pp ON ni.PublishedProcessId = pp.Id
INNER JOIN Processes p ON pp.[ProcessId] = p.[Id]
AND p.IsInactive = 0
WHERE ni.NavigationGroupId = COALESCE(@groupId, NavigationGroupId)
";
var result = new List<NavigationItemDTO>();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (var cmd = new SqlCommand(Sql, cn))
{
cmd.Parameters.AddWithValue("@groupId", groupId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
result.Add(new NavigationItemDTO
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
ProcessId = reader.GetNullableInt(2)
});
}
}
}
}
return result;
}
/// <summary>
/// Fetches the icons by category. Default - Medium.
/// </summary>
/// <param name="categoryId">The category unique identifier.</param>
/// <returns>IList{IconInfoDto}.</returns>
public IList<IconInfoDto> FetchIcons(int categoryId = (int)IconCategory.Medium)
{
var result = new List<IconInfoDto>();
const string sql = @"
SELECT
[Id]
,[Name]
,[Icon]
FROM [dbo].[Icons]
WHERE [CategoryId] = @categoryId
ORDER BY [Name]";
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (var cmd = new SqlCommand(sql, cn))
{
cmd.Parameters.AddWithValue("@categoryId", categoryId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var dto = new IconInfoDto
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Icon = AdoHelper.ReadImage(reader, 2)
};
result.Add(dto);
}
}
}
}
return result;
}
public static void GetIMActionList()
{
using (SqlConnection cn = new SqlConnection(Database.WaldenConnect))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "select Description"
+ " from IMActions"
+ " where AccountID =" + Common.AccountID
+ " order by Description";
using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
_imActions.Add(dr.GetString(0));
}
}
}
}
}
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>
/// The copy icons.
/// </summary>
public override Dictionary<string, byte[]> CopyIcons()
{
var values = new List<string>();
var items = new Dictionary<string, byte[]>();
const string commandText = @"
SELECT [Id]
,[Name]
,[Description]
,[Icon]
,[fa_alternative]
FROM [dbo].[Icons]
";
using (var ctx = GetMetaDatabaseConnectionManager())
{
using (var cmd = new SqlCommand(commandText, ctx.Connection))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var filenameOrigin = reader.GetString(1);
var data = reader.GetBytes(3);
var filename = string.Format("{0}.png", filenameOrigin.Trim());
if (!items.ContainsKey(filename) && filename != ".png")
{
items.Add(filename, (byte[])data);
}
values.Add(string.Format("({0},'{1}','{2}', NULL,'{3}')", reader.GetInt32(0), filenameOrigin.Trim(), reader.GetString(2), reader.GetString(4)));
}
}
}
}
var script = string.Format(@" SET IDENTITY_INSERT __Icon ON
MERGE INTO [dbo].[__Icon] AS TARGET USING (
VALUES {0}
)
AS Source([Id], [Name], [Description], [Icon], [fa_alternative]) ON TARGET.id = Source.Id -- update matched rows
WHEN MATCHED THEN
UPDATE
SET [Name] = Source.[Name],
[Description] = Source.[Description],
[Icon] = NULL,
[fa_alternative] = Source.[fa_alternative],
[IsRemoved] = 0 -- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Id],
[Name],
[Description],
[Icon],
[fa_alternative],
[IsRemoved])
VALUES ([Id],
[Name],
[Description],
NULL,
[fa_alternative],
0)
-- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;
SET IDENTITY_INSERT __Icon OFF
", string.Join(",", values));
ExecuteSql(script);
return items;
}
/// <summary>
/// Retrieves the view definitions from runtime database for specified process.
/// </summary>
/// <param name="process">
/// The process.
/// </param>
/// <returns>
/// The collection of view definitions.
/// </returns>
private IEnumerable<ViewDefinition> GetExistingViews(IProcessDefinition process)
{
const char EscapeCharacter = '!';
var viewPrefix = QueryGeneratorUtils.GetViewNamePrefix(process.RootTable.Name);
var commandText = string.Format(CultureInfo.InvariantCulture, @"
SELECT
vw.[name]
,m.[definition]
FROM
[sys].[views] vw
INNER JOIN [sys].[sql_modules] m ON m.[object_id] = vw.[object_id]
WHERE vw.[name] LIKE '{0}%' ESCAPE '{1}'", AdoHelper.EscapeLikePattern(viewPrefix, EscapeCharacter), EscapeCharacter);
using (var connectionManager = GetRuntimeDatabaseConnectionManager())
{
using (var cmd = new SqlCommand(commandText, connectionManager.Connection))
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
var viewDefinitions = new List<ViewDefinition>();
while (reader.Read())
{
viewDefinitions.Add(new ViewDefinition { Name = reader.GetString(0), Definition = reader.GetString(1) });
}
return viewDefinitions;
}
}
}
}
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;
}
}
}
}
public static void GetSortingProcessList(object o)
{
SortingProcessList sortingProcessList = new SortingProcessList();
using (var cn = new MySqlConnection(AppUtility.AppUtil._LocalConnectionString))
{
cn.Open();
MySqlTransaction tran = cn.BeginTransaction();
using (var cm = cn.CreateCommand())
{
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "P_SORTLINE_Efficiency";
using (var dr = new Csla.Data.SafeDataReader(cm.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 = DateTime.Now;
sortingProcess.EFFICIENCY = dr.GetDouble("EFFICIENCY");
sortingProcess.Progress = dr.GetDouble("Progress");
sortingProcessList.Add(sortingProcess);
}
}
}
try
{
foreach (SortingProcessInfo sortingProcessInfo in sortingProcessList)
{
if (!sortingProcessInfo.IsExist())
{
using (var cm = cn.CreateCommand())
{
cm.Transaction = tran;
cm.CommandType = CommandType.Text;
StringBuilder SQL = new StringBuilder();
SQL.Append("INSERT ");
SQL.Append(" INTO t_sortline_process ");
SQL.Append(" ( ");
SQL.Append(
" ID,SORTINGTASKNO,ORDERDATE,PICKLINECODE,PICKLINENAME,QTY_PRODCUT_TOT,QTY_ROUTE_TOT,QTY_CUSTOMER_TOT,QTY_PRODUCT,QTY_ROUTE,QTY_CUSTOMER,CUSTOMER_CODE,CUSTOMER_DESC,ROUTE_CODE,ROUTE_NAME,EFFICIENCY,RECEIVE_TIME,Progress ");
SQL.Append(" ) ");
SQL.Append(" VALUES ");
SQL.Append(" ( ");
SQL.Append(
" @ID,@SORTINGTASKNO,@SORT_DATE,@SORTLINE_CODE,@SORTLINE_DESC,@QTY_PRODCUT_TOT,@QTY_ROUTE_TOT,@QTY_CUSTOMER_TOT,@QTY_PRODUCT,@QTY_ROUTE,@QTY_CUSTOMER,@CUSTOMER_CODE,@CUSTOMER_DESC,@ROUTE_CODE,@ROUTE_NAME,@EFFICIENCY,@RECEIVE_TIME,@Progress ");
SQL.Append(" )");
cm.CommandText = SQL.ToString();
cm.Parameters.AddWithValue("@ID", Guid.NewGuid().ToString());
cm.Parameters.AddWithValue("@SORTINGTASKNO", sortingProcessInfo.SORTINGTASKNO);
cm.Parameters.AddWithValue("@SORT_DATE", sortingProcessInfo.ORDERDATE);
cm.Parameters.AddWithValue("@SORTLINE_CODE", sortingProcessInfo.PICKLINECODE);
cm.Parameters.AddWithValue("@SORTLINE_DESC", sortingProcessInfo.PICKLINENAME);
cm.Parameters.AddWithValue("@QTY_PRODCUT_TOT", sortingProcessInfo.QTY_PRODCUT_TOT);
cm.Parameters.AddWithValue("@QTY_ROUTE_TOT", sortingProcessInfo.QTY_ROUTE_TOT);
cm.Parameters.AddWithValue("@QTY_CUSTOMER_TOT", sortingProcessInfo.QTY_CUSTOMER_TOT);
cm.Parameters.AddWithValue("@QTY_PRODUCT", sortingProcessInfo.QTY_PRODUCT);
cm.Parameters.AddWithValue("@QTY_ROUTE", sortingProcessInfo.QTY_ROUTE);
cm.Parameters.AddWithValue("@QTY_CUSTOMER", sortingProcessInfo.QTY_CUSTOMER);
cm.Parameters.AddWithValue("@CUSTOMER_CODE", sortingProcessInfo.CUSTOMER_CODE);
cm.Parameters.AddWithValue("@CUSTOMER_DESC", sortingProcessInfo.CUSTOMER_DESC);
cm.Parameters.AddWithValue("@ROUTE_CODE", sortingProcessInfo.ROUTE_CODE);
cm.Parameters.AddWithValue("@ROUTE_NAME", sortingProcessInfo.ROUTE_NAME);
cm.Parameters.AddWithValue("@EFFICIENCY", sortingProcessInfo.EFFICIENCY);
cm.Parameters.AddWithValue("@RECEIVE_TIME", sortingProcessInfo.RECEIVE_TIME);
cm.Parameters.AddWithValue("@Progress", sortingProcessInfo.Progress);
cm.ExecuteNonQuery();
}
}
else
{
using (var cm = cn.CreateCommand())
{
cm.Transaction = tran;
cm.CommandType = CommandType.Text;
StringBuilder SQL = new StringBuilder();
if (sortingProcessInfo.PICKLINECODE == o.ToString())
{
SQL.Append("UPDATE t_sortline_process ");
SQL.Append(
" SET PICKLINENAME = @SORTLINE_DESC,QTY_PRODCUT_TOT = @QTY_PRODCUT_TOT,QTY_ROUTE_TOT = @QTY_ROUTE_TOT,QTY_CUSTOMER_TOT = @QTY_CUSTOMER_TOT,QTY_PRODUCT = @QTY_PRODUCT,QTY_ROUTE = @QTY_ROUTE,QTY_CUSTOMER = @QTY_CUSTOMER,CUSTOMER_CODE = @CUSTOMER_CODE,CUSTOMER_DESC = @CUSTOMER_DESC,ROUTE_CODE = @ROUTE_CODE, ");
SQL.Append(
" ROUTE_NAME = @ROUTE_NAME,RECEIVE_TIME = @RECEIVE_TIME,Progress=@Progress");
SQL.Append(
" WHERE SORTINGTASKNO = @SORTINGTASKNO and ORDERDATE = @SORT_DATE and PICKLINECODE = @SORTLINE_CODE");
}
else
{
SQL.Append("UPDATE t_sortline_process ");
SQL.Append(
" SET PICKLINENAME = @SORTLINE_DESC,QTY_PRODCUT_TOT = @QTY_PRODCUT_TOT,QTY_ROUTE_TOT = @QTY_ROUTE_TOT,QTY_CUSTOMER_TOT = @QTY_CUSTOMER_TOT,QTY_PRODUCT = @QTY_PRODUCT,QTY_ROUTE = @QTY_ROUTE,QTY_CUSTOMER = @QTY_CUSTOMER,CUSTOMER_CODE = @CUSTOMER_CODE,CUSTOMER_DESC = @CUSTOMER_DESC,ROUTE_CODE = @ROUTE_CODE, ");
SQL.Append(
" ROUTE_NAME = @ROUTE_NAME,Progress=@Progress ");
SQL.Append(" WHERE SORTINGTASKNO = @SORTINGTASKNO and ORDERDATE = @SORT_DATE and PICKLINECODE = @SORTLINE_CODE");
}
cm.CommandText = SQL.ToString();
cm.Parameters.AddWithValue("@SORTINGTASKNO", sortingProcessInfo.SORTINGTASKNO);
cm.Parameters.AddWithValue("@SORT_DATE", sortingProcessInfo.ORDERDATE);
cm.Parameters.AddWithValue("@SORTLINE_CODE", sortingProcessInfo.PICKLINECODE);
cm.Parameters.AddWithValue("@SORTLINE_DESC", sortingProcessInfo.PICKLINENAME);
cm.Parameters.AddWithValue("@QTY_PRODCUT_TOT", sortingProcessInfo.QTY_PRODCUT_TOT);
cm.Parameters.AddWithValue("@QTY_ROUTE_TOT", sortingProcessInfo.QTY_ROUTE_TOT);
cm.Parameters.AddWithValue("@QTY_CUSTOMER_TOT", sortingProcessInfo.QTY_CUSTOMER_TOT);
cm.Parameters.AddWithValue("@QTY_PRODUCT", sortingProcessInfo.QTY_PRODUCT);
cm.Parameters.AddWithValue("@QTY_ROUTE", sortingProcessInfo.QTY_ROUTE);
cm.Parameters.AddWithValue("@QTY_CUSTOMER", sortingProcessInfo.QTY_CUSTOMER);
cm.Parameters.AddWithValue("@CUSTOMER_CODE", sortingProcessInfo.CUSTOMER_CODE);
cm.Parameters.AddWithValue("@CUSTOMER_DESC", sortingProcessInfo.CUSTOMER_DESC);
cm.Parameters.AddWithValue("@ROUTE_CODE", sortingProcessInfo.ROUTE_CODE);
cm.Parameters.AddWithValue("@ROUTE_NAME", sortingProcessInfo.ROUTE_NAME);
cm.Parameters.AddWithValue("@Progress", sortingProcessInfo.Progress);
if (sortingProcessInfo.PICKLINECODE == o.ToString())
{
cm.Parameters.AddWithValue("@RECEIVE_TIME", sortingProcessInfo.RECEIVE_TIME);
}
cm.ExecuteNonQuery();
}
}
}
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
throw;
}
}
//return sortingProcessList;
}
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;
}
private void BindAttackData()
{
using (SqlConnection cn = new SqlConnection( Database.AphelionTriggerConnection ))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "GetAttackData";
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.AddWithValue( "@AttackerHouseID", _attackerHouseId );
cm.Parameters.AddWithValue( "@DefenderHouseID", _defenderHouseId );
using (SafeDataReader dr = new SafeDataReader( cm.ExecuteReader() ))
{
while (dr.Read())
{
_attackerHouse = new House(
dr.GetInt32( "ID" ),
dr.GetInt32( "UserID" ),
dr.GetInt32( "FactionID" ),
dr.GetString( "Faction" ),
dr.GetString( "FactionDisplay" ),
dr.GetString( "Name" ),
dr.GetInt32( "Intelligence" ),
dr.GetInt32( "Power" ),
dr.GetInt32( "Protection" ),
dr.GetInt32( "Affluence" ),
dr.GetInt32( "Speed" ),
dr.GetInt32( "Contingency" ),
dr.GetInt32( "LevelID" ),
(double)dr.GetDecimal( "Ambition" ),
dr.GetInt32( "Turns" ),
dr.GetInt32( "Credits" ),
dr.GetInt32( "MilitiaCount" ),
dr.GetInt32( "MilitaryCount" ),
dr.GetInt32( "MercenaryCount" ),
dr.GetInt32( "AgentCount" ),
dr.GetInt32( "Rank" ),
dr.GetInt32( "LastRank" ),
dr.GetInt32( "Points" ),
dr.GetInt32( "Experience" ),
dr.GetInt32( "Attack" ),
dr.GetInt32( "Defense" ),
dr.GetInt32( "Capture" ),
dr.GetInt32( "Plunder" ),
dr.GetInt32( "Stun" ),
dr.GetInt32( "GuildID" ),
dr.GetString( "Guild" ),
dr.GetString( "SmallFactionIconPath" ),
dr.GetInt32( "VotedFactionLeaderHouseID" ),
dr.GetInt32( "FactionLeaderHouseID" ),
dr.GetInt32( "FactionVotingPower" ) );
}
if (dr.NextResult())
{
while (dr.Read())
{
if (dr.GetInt32( "Count" ) > 0)
{
_attackerForces.Add( new Unit(
dr.GetInt32( "ID" ),
dr.GetInt32( "FactionID" ),
dr.GetInt32( "UnitClassID" ),
dr.GetString( "Name" ),
dr.GetString( "Description" ),
dr.GetString( "Faction" ),
dr.GetString( "UnitClass" ),
dr.GetInt32( "Cost" ),
dr.GetInt32( "Attack" ),
dr.GetInt32( "Defense" ),
dr.GetInt32( "Plunder" ),
dr.GetInt32( "Capture" ),
dr.GetInt32( "Stun" ),
dr.GetInt32( "Experience" ),
dr.GetDecimal( "RepopulationRate" ),
dr.GetDecimal( "DepopulationRate" ),
dr.GetInt32( "Count" ),
dr.GetInt32( "AttackTech" ),
dr.GetInt32( "DefenseTech" ),
dr.GetInt32( "CaptureTech" ),
dr.GetInt32( "PlunderTech" ),
dr.GetInt32( "StunTech" ),
dr.GetInt32( "ExperienceTech" ),
dr.GetDecimal( "RepopulationRateTech" ),
dr.GetDecimal( "DepopulationRateTech" ) ) );
}
}
}
else
{
throw new DataPortalException( "Attacker House has no forces to attack with.", this );
}
if (dr.NextResult())
{
while (dr.Read())
{
_attackerTechnologies.Add( new Technology(
dr.GetInt32( "ID" ),
dr.GetInt32( "FactionID" ),
dr.GetInt32( "HouseID" ),
dr.GetInt32( "GuildID" ),
dr.GetInt32( "TechnologyTypeID" ),
dr.GetInt32( "UnitID" ),
dr.GetInt32( "UnitClassID" ),
dr.GetString( "Name" ),
dr.GetString( "Faction" ),
dr.GetString( "House" ),
dr.GetString( "Guild" ),
dr.GetString( "Description" ),
dr.GetString( "TechnologyType" ),
dr.GetString( "Unit" ),
dr.GetString( "UnitClass" ),
dr.GetInt32( "Attack" ),
dr.GetInt32( "Defense" ),
dr.GetInt32( "Plunder" ),
dr.GetInt32( "Capture" ),
dr.GetInt32( "Stun" ),
dr.GetInt32( "Experience" ),
dr.GetDecimal( "RepopulationRate" ),
dr.GetDecimal( "DepopulationRate" ),
dr.GetInt32( "ResearchCost" ),
dr.GetInt32( "ResearchTime" ),
dr.GetInt32( "ResearchTurns" ),
dr.GetInt32( "TimeSpent" ),
dr.GetInt32( "TurnsSpent" ),
dr.GetInt32( "CreditsSpent" ),
dr.GetInt32( "ResearchStateID" ),
dr.GetString( "ResearchState" ),
dr.GetSmartDate( "ResearchStartedDate" ) ) );
}
}
if (dr.NextResult())
{
while (dr.Read())
{
_defenderHouse = new House(
dr.GetInt32( "ID" ),
dr.GetInt32( "UserID" ),
dr.GetInt32( "FactionID" ),
dr.GetString( "Faction" ),
dr.GetString( "FactionDisplay" ),
dr.GetString( "Name" ),
dr.GetInt32( "Intelligence" ),
dr.GetInt32( "Power" ),
dr.GetInt32( "Protection" ),
dr.GetInt32( "Affluence" ),
dr.GetInt32( "Speed" ),
dr.GetInt32( "Contingency" ),
dr.GetInt32( "LevelID" ),
(double)dr.GetDecimal( "Ambition" ),
dr.GetInt32( "Turns" ),
dr.GetInt32( "Credits" ),
dr.GetInt32( "MilitiaCount" ),
dr.GetInt32( "MilitaryCount" ),
dr.GetInt32( "MercenaryCount" ),
dr.GetInt32( "AgentCount" ),
dr.GetInt32( "Rank" ),
dr.GetInt32( "LastRank" ),
dr.GetInt32( "Points" ),
dr.GetInt32( "Experience" ),
dr.GetInt32( "Attack" ),
dr.GetInt32( "Defense" ),
dr.GetInt32( "Capture" ),
dr.GetInt32( "Plunder" ),
dr.GetInt32( "Stun" ),
dr.GetInt32( "GuildID" ),
dr.GetString( "Guild" ),
dr.GetString( "SmallFactionIconPath" ),
dr.GetInt32( "VotedFactionLeaderHouseID" ),
dr.GetInt32( "FactionLeaderHouseID" ),
dr.GetInt32( "FactionVotingPower" ) );
}
}
else
{
throw new DataPortalException( "Defender House not found.", this );
}
if (dr.NextResult())
{
while (dr.Read())
{
if (dr.GetInt32( "Count" ) > 0)
{
_defenderForces.Add( new Unit(
dr.GetInt32( "ID" ),
dr.GetInt32( "FactionID" ),
dr.GetInt32( "UnitClassID" ),
dr.GetString( "Name" ),
dr.GetString( "Description" ),
dr.GetString( "Faction" ),
dr.GetString( "UnitClass" ),
dr.GetInt32( "Cost" ),
dr.GetInt32( "Attack" ),
dr.GetInt32( "Defense" ),
dr.GetInt32( "Plunder" ),
dr.GetInt32( "Capture" ),
dr.GetInt32( "Stun" ),
dr.GetInt32( "Experience" ),
dr.GetDecimal( "RepopulationRate" ),
dr.GetDecimal( "DepopulationRate" ),
dr.GetInt32( "Count" ),
dr.GetInt32( "AttackTech" ),
dr.GetInt32( "DefenseTech" ),
dr.GetInt32( "CaptureTech" ),
dr.GetInt32( "StunTech" ),
dr.GetInt32( "PlunderTech" ),
dr.GetInt32( "ExperienceTech" ),
dr.GetDecimal( "RepopulationRateTech" ),
dr.GetDecimal( "DepopulationRateTech" ) ) );
}
}
}
if (dr.NextResult())
{
while (dr.Read())
{
_defenderTechnologies.Add( new Technology(
dr.GetInt32( "ID" ),
dr.GetInt32( "FactionID" ),
dr.GetInt32( "HouseID" ),
dr.GetInt32( "GuildID" ),
dr.GetInt32( "TechnologyTypeID" ),
dr.GetInt32( "UnitID" ),
dr.GetInt32( "UnitClassID" ),
dr.GetString( "Name" ),
dr.GetString( "Faction" ),
dr.GetString( "House" ),
dr.GetString( "Guild" ),
dr.GetString( "Description" ),
dr.GetString( "TechnologyType" ),
dr.GetString( "Unit" ),
dr.GetString( "UnitClass" ),
dr.GetInt32( "Attack" ),
dr.GetInt32( "Defense" ),
dr.GetInt32( "Plunder" ),
dr.GetInt32( "Capture" ),
dr.GetInt32( "Stun" ),
dr.GetInt32( "Experience" ),
dr.GetDecimal( "RepopulationRate" ),
dr.GetDecimal( "DepopulationRate" ),
dr.GetInt32( "ResearchCost" ),
dr.GetInt32( "ResearchTime" ),
dr.GetInt32( "ResearchTurns" ),
dr.GetInt32( "TimeSpent" ),
dr.GetInt32( "TurnsSpent" ),
dr.GetInt32( "CreditsSpent" ),
dr.GetInt32( "ResearchStateID" ),
dr.GetString( "ResearchState" ),
dr.GetSmartDate( "ResearchStartedDate" ) ) );
}
}
}
}
}
}
protected void Child_Fetch(Csla.Data.SafeDataReader reader)
{
LoadProperty(firstNameProperty, reader.GetString("FirstName"));
LoadProperty(lastNameProperty, reader.GetString("LastName"));
LoadProperty(smallColumnProperty, reader.GetString("SmallColumn"));
}
public EDCEditDto FetchEdcBySystemName(string systemname)
{
const string sql = @"
SELECT [Id]
,[Name]
,[SystemName]
,[Documentation]
,[GuidId]
FROM [dbo].[ExternalDataConfiguration] edc
WHERE [SystemName] = @sysName
";
var result = new EDCEditDto();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (var cmd = new SqlCommand(sql, cn))
{
cmd.Parameters.AddWithValue("@sysName", systemname);
using (var sr = new SafeDataReader(cmd.ExecuteReader()))
{
while (sr.Read())
{
var parameter = new EDCEditDto
{
Id = sr.GetInt32(0),
Name = sr.GetString(1),
SystemName = sr.GetString(2),
Documentation = sr.GetString(3),
Guid = sr.GetGuid(4)
};
result = parameter;
}
}
result.ConnectionList = new List<EDCConnectionEditDto>();
result.DataVariableList = new List<EDCDataVariablesEditDto>();
FetchEDCConnectionList(cn, result);
FetchDataVariableList(cn, result);
}
}
return result;
}
public 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 Product Map(SafeDataReader reader)
{
var item = (Product)Activator.CreateInstance(typeof(Product), true);
using (BypassPropertyChecks(item))
{
item.ProductId = reader.GetString("ProductId");
item.OriginalProductId = reader.GetString("ProductId");
item.CategoryId = reader.GetString("CategoryId");
item.Name = reader.GetString("Name");
item.Description = reader.GetString("Descn");
item.Image = reader.GetString("Image");
}
MarkOld(item);
return item;
}
/// <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 static ArrayList GetPatientChartFilterList()
{
ArrayList _filterList = new ArrayList();
using (SqlConnection cn = new SqlConnection(Database.WaldenConnect))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandText = "select Name, Description,FilterID"
+ " from PatientChartFilter"
+ " where AccountID =" + Common.AccountID
+ " and FilterId > 0"
+ " order by Description";
using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
{
while (dr.Read())
{
_filterList.Add(dr.GetString(0) + "~"
+ dr.GetString(1) + "~"
+ dr.GetInt32(2));
}
return _filterList;
}
}
}
}
