/// <summary>
/// Reads required rule config fields.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
/// ProcessCommandSecurityConfigurations
private static void ReadRequiredRuleConfigFields(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
int? requiredConfigId = null;
RequiredRuleConfigEditDto config = null;
while (sr.Read())
{
var configFieldDto = new RequiredRuleConfigFieldEditDto
{
Id = sr.GetInt32(0),
RequiredConfigId = sr.GetInt32(1),
Guid = sr.GetGuid(2),
FieldGuid = sr.GetGuid(3),
IsRequired = sr.GetBoolean(4)
};
if (configFieldDto.RequiredConfigId != requiredConfigId)
{
config = process.RequiredRuleConfigs.First(rrc => rrc.Id == configFieldDto.RequiredConfigId);
requiredConfigId = configFieldDto.RequiredConfigId;
}
config.FieldList.Add(configFieldDto);
}
}
public 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 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;
}
}
}
}
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 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;
}
/// <summary>
/// Retrieves dashboard DTO object for a given account id.
/// </summary>
/// <param name="accountId">The account id.</param>
/// <returns>The <see cref="DashboardDto" />.</returns>
public DashboardDto FetchDashboard(int accountId)
{
var result = new DashboardDto();
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
{
var cn = ctx.Connection;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
const string CommandText = @"
SELECT [Id]
, [SelectedPageIndex]
FROM [dbo].[Dashboards]
WHERE [AccountId] = @accountId
";
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (var cmd = new SqlCommand(CommandText, cn))
{
cmd.Parameters.AddWithValue("@accountId", accountId);
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
if (reader.Read())
{
result.Id = reader.GetInt32(0);
result.SelectedPageIndex = reader.GetInt32(1);
}
else return null;
}
}
ReadDashboardPages(result);
return result;
}
}
/// <summary>
/// Reads search display fields.
/// </summary>
/// <param name="process">The process.</param>
/// <param name="sr">The reader.</param>
private static void ReadSearchDisplayFields(ProcessEditDto process, SafeDataReader sr)
{
sr.NextResult();
while (sr.Read())
{
process.Search.Add(
new ProcessSearchEditDto
{
Id = sr.GetInt32(0),
ProcessId = sr.GetInt32(1),
GuidId = sr.GetGuid(2),
FieldSystemName = sr.GetString(3),
Position = sr.GetInt32(4),
Width = sr.GetInt32(5),
ShowInList = sr.GetBoolean(6)
});
}
}
public 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;
}
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 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),
};
}
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;
}
/// <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 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);
}
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 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 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>
/// 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);
}
}
protected override void DataPortal_Insert()
{
bool cancel = false;
OnInserting(ref cancel);
if (cancel) return;
const string commandText = "INSERT INTO [dbo].[Profiles] ([Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]) VALUES (@p_Username, @p_ApplicationName, @p_IsAnonymous, @p_LastActivityDate, @p_LastUpdatedDate); SELECT [UniqueID] FROM [dbo].[Profiles] WHERE UniqueID = SCOPE_IDENTITY()";
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using(var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddWithValue("@p_Username", this.Username);
command.Parameters.AddWithValue("@p_ApplicationName", this.ApplicationName);
command.Parameters.AddWithValue("@p_IsAnonymous", ADOHelper.NullCheck(this.IsAnonymous));
command.Parameters.AddWithValue("@p_LastActivityDate", ADOHelper.NullCheck(this.LastActivityDate));
command.Parameters.AddWithValue("@p_LastUpdatedDate", ADOHelper.NullCheck(this.LastUpdatedDate));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
using (BypassPropertyChecks)
{
LoadProperty(_uniqueIDProperty, reader.GetInt32("UniqueID"));
}
}
}
}
FieldManager.UpdateChildren(this, connection);
}
OnInserted();
}
private void Child_Insert(SqlConnection connection)
{
bool cancel = false;
OnChildInserting(connection, ref cancel);
if (cancel) return;
if(connection.State != ConnectionState.Open) connection.Open();
const string commandText = "INSERT INTO [dbo].[Profiles] ([Username], [ApplicationName], [IsAnonymous], [LastActivityDate], [LastUpdatedDate]) VALUES (@p_Username, @p_ApplicationName, @p_IsAnonymous, @p_LastActivityDate, @p_LastUpdatedDate); SELECT [UniqueID] FROM [dbo].[Profiles] WHERE UniqueID = SCOPE_IDENTITY()";
using(var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddWithValue("@p_Username", this.Username);
command.Parameters.AddWithValue("@p_ApplicationName", this.ApplicationName);
command.Parameters.AddWithValue("@p_IsAnonymous", ADOHelper.NullCheck(this.IsAnonymous));
command.Parameters.AddWithValue("@p_LastActivityDate", ADOHelper.NullCheck(this.LastActivityDate));
command.Parameters.AddWithValue("@p_LastUpdatedDate", ADOHelper.NullCheck(this.LastUpdatedDate));
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
// Update identity primary key value.
LoadProperty(_uniqueIDProperty, reader.GetInt32("UniqueID"));
}
}
}
FieldManager.UpdateChildren(this, connection);
OnChildInserted();
}
protected override void DataPortal_Insert()
{
bool cancel = false;
OnInserting(ref cancel);
if (cancel) return;
const string commandText = "INSERT INTO [dbo].[Orders] ([UserId], [OrderDate], [ShipAddr1], [ShipAddr2], [ShipCity], [ShipState], [ShipZip], [ShipCountry], [BillAddr1], [BillAddr2], [BillCity], [BillState], [BillZip], [BillCountry], [Courier], [TotalPrice], [BillToFirstName], [BillToLastName], [ShipToFirstName], [ShipToLastName], [AuthorizationNumber], [Locale]) VALUES (@p_UserId, @p_OrderDate, @p_ShipAddr1, @p_ShipAddr2, @p_ShipCity, @p_ShipState, @p_ShipZip, @p_ShipCountry, @p_BillAddr1, @p_BillAddr2, @p_BillCity, @p_BillState, @p_BillZip, @p_BillCountry, @p_Courier, @p_TotalPrice, @p_BillToFirstName, @p_BillToLastName, @p_ShipToFirstName, @p_ShipToLastName, @p_AuthorizationNumber, @p_Locale); SELECT [OrderId] FROM [dbo].[Orders] WHERE OrderId = SCOPE_IDENTITY()";
using (var connection = new SqlConnection(ADOHelper.ConnectionString))
{
connection.Open();
using(var command = new SqlCommand(commandText, connection))
{
command.Parameters.AddWithValue("@p_UserId", this.UserId);
command.Parameters.AddWithValue("@p_OrderDate", this.OrderDate);
command.Parameters.AddWithValue("@p_ShipAddr1", this.ShipAddr1);
command.Parameters.AddWithValue("@p_ShipAddr2", ADOHelper.NullCheck(this.ShipAddr2));
command.Parameters.AddWithValue("@p_ShipCity", this.ShipCity);
command.Parameters.AddWithValue("@p_ShipState", this.ShipState);
command.Parameters.AddWithValue("@p_ShipZip", this.ShipZip);
command.Parameters.AddWithValue("@p_ShipCountry", this.ShipCountry);
command.Parameters.AddWithValue("@p_BillAddr1", this.BillAddr1);
command.Parameters.AddWithValue("@p_BillAddr2", ADOHelper.NullCheck(this.BillAddr2));
command.Parameters.AddWithValue("@p_BillCity", this.BillCity);
command.Parameters.AddWithValue("@p_BillState", this.BillState);
command.Parameters.AddWithValue("@p_BillZip", this.BillZip);
command.Parameters.AddWithValue("@p_BillCountry", this.BillCountry);
command.Parameters.AddWithValue("@p_Courier", this.Courier);
command.Parameters.AddWithValue("@p_TotalPrice", this.TotalPrice);
command.Parameters.AddWithValue("@p_BillToFirstName", this.BillToFirstName);
command.Parameters.AddWithValue("@p_BillToLastName", this.BillToLastName);
command.Parameters.AddWithValue("@p_ShipToFirstName", this.ShipToFirstName);
command.Parameters.AddWithValue("@p_ShipToLastName", this.ShipToLastName);
command.Parameters.AddWithValue("@p_AuthorizationNumber", this.AuthorizationNumber);
command.Parameters.AddWithValue("@p_Locale", this.Locale);
using(var reader = new SafeDataReader(command.ExecuteReader()))
{
if(reader.Read())
{
using (BypassPropertyChecks)
{
LoadProperty(_orderIdProperty, reader.GetInt32("OrderId"));
}
}
}
}
FieldManager.UpdateChildren(this, connection);
}
OnInserted();
}
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" ) ) );
}
}
}
}
}
}
private static void FetchEDCConnectionList(SqlConnection connection, EDCEditDto dto)
{
const string Sql =
@"
SELECT
[Id]
,[GuidId]
,[EDCId]
,[ConnectionSetupId]
,[FirstExpression]
FROM
[dbo].[EDCConnections]
WHERE [EDCId] = @edcId;
";
using (var cmd = new SqlCommand(Sql, connection))
{
cmd.Parameters.AddWithValue("@edcId", dto.Id);
var result = new List<EDCConnectionEditDto>();
try
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var param = new EDCConnectionEditDto
{
Id = reader.GetInt(0),
Guid = reader.GetGuid(1),
EDCId = reader.GetInt32(2),
ConnectionSetupId = reader.GetInt32(3),
FirstExpression = reader.GetString(4)
};
result.Add(param);
}
foreach (var editDto in result)
{
editDto.Connection = new ConnectionSettingsDto();
FetchConnectionSetting(connection, editDto);
}
dto.ConnectionList.AddRange(result);
}
}
catch (Exception ex)
{
throw new ArgumentException(ex.Data.ToString());
}
}
}
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;
}
private static void FetchDataVariableList(SqlConnection connection, EDCEditDto dto)
{
const string Sql =
@"
SELECT
[Id]
,[GuidId]
,[EDCId]
,[VariableName]
,[VariableSystemName]
,[VariableDataType]
FROM
[dbo].[EDCDataVariables]
WHERE [EDCId] = @edcId;
";
using (var cmd = new SqlCommand(Sql, connection))
{
cmd.Parameters.AddWithValue("@edcId", dto.Id);
try
{
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
while (reader.Read())
{
var dataVariableType = DataVariablesType.None;
var type = reader.GetString(5);
if (!string.IsNullOrWhiteSpace(type))
{
if (type == "Double")
{
dataVariableType = DataVariablesType.Decimal;
}
else
{
dataVariableType = !string.IsNullOrEmpty(reader.GetString(5))
? (DataVariablesType)Enum.Parse(typeof(DataVariablesType), reader.GetString(5))
: DataVariablesType.None;
}
}
var edcDataVariablesEditDto = new EDCDataVariablesEditDto
{
Id = reader.GetInt(0),
Guid = reader.GetGuid(1),
EDCId = reader.GetInt32(2),
VariableName = reader.GetString(3),
VariableSystemName = reader.GetString(4),
VariableDataType = dataVariableType
};
dto.DataVariableList.Add(edcDataVariablesEditDto);
}
}
}
catch (Exception ex)
{
throw new ArgumentException(ex.Data.ToString());
}
}
}
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;
}
}
}
}
