private Select GetSysImageBySchemaNameSelect(Guid schemaUId, Guid?notificationTypeId)
{
var select = new Select(_userConnection);
select
.Top(1)
.Column("NotificationsSettings", "SysImageId").As("Id")
.From("NotificationsSettings")
.InnerJoin("VwSysSchemaInWorkspace")
.On("VwSysSchemaInWorkspace", "UId").IsEqual("NotificationsSettings", "SysEntitySchemaUId")
.Where("VwSysSchemaInWorkspace", "UId")
.IsEqual(Column.Parameter(schemaUId))
.And("VwSysSchemaInWorkspace", "SysWorkspaceId")
.IsEqual(Column.Parameter(_userConnection.Workspace.PrimaryColumnValue));
if (notificationTypeId.IsNullOrEmpty())
{
select.And("NotificationsSettings", "NotificationTypeId").IsNull();
}
else
{
select.And("NotificationsSettings", "NotificationTypeId")
.IsEqual(Column.Parameter(notificationTypeId));
}
return(select);
}
/// <summary>
/// Adds additional export filters.
/// </summary>
/// <param name="context">Synchronization context.</param>
/// <param name="select"><see cref="Select"/> in which the filters will be added.</param>
public virtual void AddExportFilters(UserConnection userConnection, Select select)
{
var currentUserId = userConnection.CurrentUser.ContactId;
select.And().Not().Exists(
new Select(userConnection)
.Column("LocalId")
.From("SysSyncMetaData").As("SSMD")
.Where("SSMD", "SyncSchemaName").IsEqual(new QueryParameter("Activity"))
.And("SSMD", "LocalId").IsEqual("Activity", "Id")
.And()
.OpenBlock("SSMD", "CreatedById").IsEqual(new QueryParameter(
userConnection.CurrentUser.ContactId))
.Or()
.OpenBlock("SSMD", "RemoteItemName").IsNotEqual(new QueryParameter(
KnownTypes().First().Name))
.And("SSMD", "CreatedById").IsNotEqual(new QueryParameter(
userConnection.CurrentUser.ContactId))
.CloseBlock()
.CloseBlock()
);
if (ActivityCategoryId == ActivityConsts.AppointmentActivityCategoryId)
{
select.InnerJoin("ActivityParticipant").On("Activity", "Id").IsEqual("ActivityParticipant", "ActivityId")
.And("ActivityParticipant", "ParticipantId").IsEqual(Column.Parameter(currentUserId));
}
else
{
select.And("OwnerId").IsEqual(Column.Parameter(currentUserId));
}
}
public virtual Select CreateUnsubscribedSubQuery(Guid recordId)
{
var select = new Select(UserConnection)
.Column(Column.Const(1))
.From("BulkEmailSubscription")
.Where("ContactId").IsEqual("Contact", "Id")
.And("BulkEmailSubsStatusId").IsEqual(Column.Parameter(MarketingConsts.BulkEmailContactUnsubscribed)) as Select;
if (RootSchemaName.Equals("BulkEmailSplit"))
{
select.And("BulkEmailTypeId").In(
new Select(UserConnection)
.Column("BulkEmail", "TypeId")
.From("BulkEmail")
.InnerJoin("BulkEmailType").On("BulkEmail", "TypeId").IsEqual("BulkEmailType", "Id")
.Where("BulkEmailType", "IsSignable").IsEqual(Column.Parameter(true))
.And("SplitTestId").IsEqual(Column.Parameter(recordId))
);
}
else
{
select.And("BulkEmailTypeId").In(
new Select(UserConnection)
.Column("TypeId")
.From("BulkEmail")
.Where("Id").IsEqual(Column.Parameter(recordId))
);
}
return(select);
}
public static List <Guid> GetExistingContactIds(UserConnection userConnection, string fullName, string email,
string phone)
{
var contactIdsQuery = new Select(userConnection)
.Column("Id")
.From("Contact")
.Where("Name").IsEqual(Column.Parameter(string.IsNullOrEmpty(fullName) ? string.Empty : fullName));
if (!string.IsNullOrEmpty(email))
{
contactIdsQuery.And("Id").In(new Select(userConnection)
.Column("ContactId").From("ContactCommunication")
.Where("Number").IsEqual(Column.Parameter(string.IsNullOrEmpty(email) ? string.Empty : email)));
}
if (!string.IsNullOrEmpty(phone))
{
contactIdsQuery.And("Id").In(new Select(userConnection)
.Column("ContactId").From("ContactCommunication")
.Where("Number").IsEqual(Column.Parameter(string.IsNullOrEmpty(phone) ? string.Empty : phone)));
}
var contactIds = new List <Guid>();
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection()) {
using (IDataReader dr = ((Select)contactIdsQuery).ExecuteReader(dbExecutor)) {
while (dr.Read())
{
string val = dr.GetValue(0).ToString();
contactIds.Add(new Guid(val));
}
}
}
return(contactIds);
}
/// <summary>
/// hàm tìm kiếm thông tin của Form
/// </summary>
private void Search()
{
try
{
SqlQuery q = new Select().From(VDmucDoituongbhyt.Schema);
q.Where("1=1");
if (!string.IsNullOrEmpty(txtInsObject_Code.Text))
{
q.And(VDmucDoituongbhyt.Columns.MaDoituongbhyt).Like("%" + txtInsObject_Code.Text + "%");
}
if (!string.IsNullOrEmpty(txtDieaseName.Text))
{
q.And(VDmucDoituongbhyt.Columns.TenDoituongbhyt).Like("%" + txtDieaseName.Text + "%");
}
if (cboObjectType_ID.SelectedIndex > 0)
{
q.And(VDmucDoituongbhyt.Columns.IdDoituongKcb).IsEqualTo(
Utility.Int32Dbnull(cboObjectType_ID.SelectedValue, -1));
}
if (cboGroupInsurance.SelectedIndex > 0)
{
q.And(VDmucDoituongbhyt.Columns.MaNhombhyt).IsEqualTo(
Utility.sDbnull(cboGroupInsurance.SelectedValue, "-1"));
}
q.OrderAsc(VDmucDoituongbhyt.Columns.SttHthi);
m_dtInsurance = q.ExecuteDataSet().Tables[0];
Utility.SetDataSourceForDataGridEx(grd_Insurance_Objects, m_dtInsurance, true, true, "", "");
}
catch (Exception)
{
}
// grd_Insurance_Objects.DataSource = m_dtInsurance;
}
void Search()
{
SqlQuery _sqlquery = new Select().From(VDmucDichvucl.Schema);
if (Utility.sDbnull(cboServiceType.SelectedValue, "-1") != "-1" || hanchequyendanhmuc)
{
if (_sqlquery.HasWhere)
{
_sqlquery.Where(VDmucDichvucl.Columns.IdLoaidichvu).IsEqualTo(Utility.sDbnull(cboServiceType.SelectedValue, hanchequyendanhmuc?"0": "-1"));
}
else
{
_sqlquery.And(VDmucDichvucl.Columns.IdLoaidichvu).IsEqualTo(Utility.sDbnull(cboServiceType.SelectedValue, hanchequyendanhmuc ? "0" : "-1"));
}
}
if (Utility.sDbnull(cbonhombaocao.SelectedValue, "-1") != "-1")
{
if (_sqlquery.HasWhere)
{
_sqlquery.Where(VDmucDichvucl.Columns.NhomBaocao).IsEqualTo(Utility.sDbnull(cbonhombaocao.SelectedValue, "-1"));
}
else
{
_sqlquery.And(VDmucDichvucl.Columns.NhomBaocao).IsEqualTo(Utility.sDbnull(cbonhombaocao.SelectedValue, "-1"));
}
}
if (Utility.Int32Dbnull(cboDepartment.SelectedValue, -1) != -1)
{
if (_sqlquery.HasWhere)
{
_sqlquery.Where(VDmucDichvucl.Columns.IdKhoaThuchien).IsEqualTo(Utility.Int32Dbnull(cboDepartment.SelectedValue, -1));
}
else
{
_sqlquery.And(VDmucDichvucl.Columns.IdKhoaThuchien).IsEqualTo(Utility.Int32Dbnull(cboDepartment.SelectedValue, -1));
}
}
if (Utility.Int32Dbnull(cboPhongthuchien.SelectedValue, -1) != -1)
{
if (_sqlquery.HasWhere)
{
_sqlquery.Where(VDmucDichvucl.Columns.IdPhongThuchien).IsEqualTo(Utility.Int32Dbnull(cboPhongthuchien.SelectedValue, -1));
}
else
{
_sqlquery.And(VDmucDichvucl.Columns.IdPhongThuchien).IsEqualTo(Utility.Int32Dbnull(cboPhongthuchien.SelectedValue, -1));
}
}
dtDataCLS = _sqlquery.ExecuteDataSet().Tables[0];
dtDataCLS.AcceptChanges();
Utility.SetDataSourceForDataGridEx(grdList, dtDataCLS, true, true, "1=1", "stt_hthi_loaidvu,ten_loaidichvu,stt_hthi,ten_dichvu");
}
/// <summary>
/// Sets ContactId from active SysAdminUnit it as ReceiverId column.
/// </summary>
/// <param name="select"></param>
private void SetReceiverIdFromActiveEmployees(Select select)
{
const string receiverColumnAlias = "ReceiverId";
QueryColumnExpression receiverColumn = select.Columns.FindByAlias(receiverColumnAlias);
select.CrossJoin("SysAdminUnit").As("sau");
int receiverIndex = select.Columns.IndexOf(receiverColumn);
receiverColumn = Column.SourceColumn("sau", "ContactId");
receiverColumn.Alias = receiverColumnAlias;
select.Columns[receiverIndex] = receiverColumn;
select.And("sau", "Active").IsEqual(Column.Parameter(true));
select.And("sau", "ContactId").Not().IsNull();
select.And("sau", "ConnectionType").IsNotEqual(Column.Parameter(UserType.SSP));
}
/// <summary>
/// Returns select with all filters for current entity.
/// <param name="select">Select for getting anniversary reminding.</param>
/// <param name="queryProperty">Query property for current select.</param>
/// <param name="queryProperty.Key">Anniversary entity schema name</param>
/// <param name="queryProperty.Value">The property determines type of the decoration.</param>
/// <returns>Modified select with all filters for current entity.</returns>
/// </summary>
protected override Select DecorateSelect(Select select, KeyValuePair <string, string> queryProperty)
{
select.And(SchemaName, "StatusId").In(Column.Parameters(Constants.OrderInProgressStatusId,
Constants.OrderDraftStatusId, Constants.OrderConfirmationStatusId));
select = AnniversaryRemindingsHelper.JoinTable(select, SchemaName, queryProperty.Key);
return(select);
}
/// <summary>
/// destinatiobn controller
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dxcboDestControl_ItemRequestedByValue(object source, ListEditItemRequestedByValueEventArgs e)
{
ASPxComboBox _combo = (ASPxComboBox)source;
ASPxComboBox _destinationagent = (ASPxComboBox)this.fmvAddresses.FindControl("dxcboAgentAtDestinationIDEdit");
if (_combo != null)
{
string[] _cols = { "EmployeesTable.EmployeeID, EmployeesTable.Name", "EmployeesTable.DepartmentID", "OfficeTable.OfficeID", " NameAndAddressBook.CompanyID" };
string[] _order = { "Name" };
SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn).
InnerJoin(DAL.Logistics.NameAndAddressBook.CountryIDColumn, DAL.Logistics.OfficeTable.CountryIDColumn);
if (_destinationagent != null && _destinationagent.SelectedItem != null && _destinationagent.Value != null)
{
int _filter = wwi_func.vint(_destinationagent.SelectedItem.GetValue("CountryID").ToString());
if (_filter > 0)
{
_qry.Where("CountryID").IsEqualTo(_filter);
}
}
_qry.And(DAL.Logistics.EmployeesTable.LiveColumn).IsEqualTo(true).OrderAsc(_order);
DataTable _dt = _qry.ExecuteDataSet().Tables[0];
IDataReader _rd1 = _qry.ExecuteReader();
_combo.DataSource = _rd1;
_combo.ValueField = "EmployeeID";
_combo.ValueType = typeof(int);
_combo.TextField = "Name";
_combo.DataBindItems();
}
}
protected void bind_destination_controller(int countryId)
{
ASPxComboBox _combo = (ASPxComboBox)this.fmvAddresses.FindControl("dxcboDestinationPortControllerIDEdit");
if (_combo != null)
{
string[] _cols = { "EmployeesTable.EmployeeID, EmployeesTable.Name", "EmployeesTable.DepartmentID", "OfficeTable.OfficeID" };
string[] _order = { "Name" };
//don't need nameandaddressbook as get countryid from officetable
//SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
// InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn).
// InnerJoin(DAL.Logistics.NameAndAddressBook.CountryIDColumn, DAL.Logistics.OfficeTable.CountryIDColumn);
SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn);
if (countryId > 0)
{
_qry.Where("CountryID").IsEqualTo(countryId);
}
_qry.And(DAL.Logistics.EmployeesTable.LiveColumn).IsEqualTo(1).OrderAsc(_order);
//string _q = _qry.ToString(); //fo testing
//DataTable _dt = _qry.ExecuteDataSet().Tables[0]; //for testing
IDataReader _rd1 = _qry.ExecuteReader();
_combo.DataSource = _rd1;
_combo.ValueField = "EmployeeID";
_combo.ValueType = typeof(int);
_combo.TextField = "Name";
_combo.DataBindItems();
}
}
private Select GetMessageResponseSelectQuery(BulkEmailCountLimit rule, int bulkEmailRId)
{
var periodStartTimeStamp = Utilities.ConvertDateTimeToTimestamp(DateTime.UtcNow.AddDays(-rule.MailingLimitPeriod));
var audienceSubQuery = new Select(_userConnection)
.Column("ContactRId")
.From("MandrillRecipient")
.Where("BulkEmailRId").IsEqual(Column.Parameter(bulkEmailRId));
if (_sessionId != Guid.Empty)
{
audienceSubQuery.And("SessionUId").IsEqual(Column.Parameter(_sessionId));
}
var distinctSelectQuery = new Select(_userConnection)
.Distinct()
.Column("ContactRId")
.Column("BulkEmailRId")
.From("MandrillMessageResponse").As("mmr")
.LeftOuterJoin("BulkEmailTarget").As("bet").On("bet", "MandrillId").IsEqual("mmr", "MandrillId")
.Where("mmr", "ResponseCode").IsNotEqual(Column.Parameter((int)MailingResponseCode.CanceledCommunicationLimit))
.And("mmr", "TimeStamp").IsGreaterOrEqual(Column.Parameter(periodStartTimeStamp))
.And("bet", "MandrillId").IsNull()
.And("mmr", "ContactRId").In(audienceSubQuery)
as Select;
var join = distinctSelectQuery.InnerJoin("BulkEmail")
.On("BulkEmail", "RId").IsEqual("mmr", "BulkEmailRId")
.And("BulkEmail", "IsSystemEmail").IsEqual(Column.Const(false));
AddBulkEmailFilter(rule, join);
var selectQuery = new Select(_userConnection)
.Column("Contact", "Id")
.From(distinctSelectQuery).As("distinctSelect")
.InnerJoin("Contact").On("Contact", "RId").IsEqual("distinctSelect", "ContactRId") as Select;
return(selectQuery);
}
private Select GetBulkEmailTargetSelectQuery(BulkEmailCountLimit rule, int bulkEmailRId)
{
var periodStartDateTime = DateTime.UtcNow.AddDays(-rule.MailingLimitPeriod);
var audienceSubQuery = new Select(_userConnection)
.Column("Contact", "Id")
.From("MandrillRecipient")
.InnerJoin("Contact").On("Contact", "RId").IsEqual("MandrillRecipient", "ContactRId")
.Where("MandrillRecipient", "BulkEmailRId").IsEqual(Column.Parameter(bulkEmailRId));
if (_sessionId != Guid.Empty)
{
audienceSubQuery.And("SessionUId").IsEqual(Column.Parameter(_sessionId));
}
var selectQuery = new Select(_userConnection)
.Column("ContactId")
.From("BulkEmailTarget").As("bet")
.Where("bet", "BulkEmailResponseId").IsNotEqual(Column.Parameter(MailingConsts.BulkEmailResponseLimitedId))
.And("bet", "CreatedOn").IsGreaterOrEqual(Column.Parameter(periodStartDateTime))
.And("bet", "ContactId").In(audienceSubQuery)
as Select;
var join = selectQuery.InnerJoin("BulkEmail")
.On("BulkEmail", "Id").IsEqual("bet", "BulkEmailId")
.And("BulkEmail", "IsSystemEmail").IsEqual(Column.Const(false));
AddBulkEmailFilter(rule, join);
return(selectQuery);
}
/// <summary>
/// Methods returns unique id for <c>Web</c> <see cref="ContactCommunication"/> record, returns first by value
/// or CreatedOn date. Returns <c>Guid.Empty</c> if records not found.
/// </summary>
/// <param name="userConnection">User connection instance.</param>
/// <param name="contactId"><see cref="Contact"/> unique id.</param>
/// <param name="value">Communication search value. Default value is <c>null</c>.</param>
/// <returns>
/// Unique id for <c>Web</c> <see cref="ContactCommunication"/> record or <c>Guid.Empty</c> if
/// records not found.
///</returns>
private static Guid TryFindWebPageDetailInLocalStore(UserConnection userConnection,
Guid contactId, string value = null)
{
var id = Guid.Empty;
var select = new Select(userConnection).Top(1)
.Column("Id")
.From("ContactCommunication")
.Where("ContactId").IsEqual(Column.Parameter(contactId))
.And("CommunicationTypeId").IsEqual(Column.Parameter(Guid.Parse(CommunicationTypeConsts.WebId)))
.OrderBy(OrderDirectionStrict.Ascending, "CreatedOn");
if (!string.IsNullOrEmpty(value))
{
select.And("SearchNumber").IsEqual(Column.Parameter(value.ToLower()));
}
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection()) {
using (IDataReader reader = dbExecutor.ExecuteReader(select.GetSqlText(), select.Parameters)) {
if (reader.Read())
{
id = reader.GetColumnValue <Guid>("Id");
}
}
}
return(id);
}
private void ExtendFilterWithEntityIdFilter(ref Select filter, Guid entityId)
{
filter = (filter.HasCondition
? filter.And(filter.Columns.First()).IsEqual(Column.Parameter(entityId))
: filter.Where(filter.Columns.First()).IsEqual(Column.Parameter(entityId))) as Select;
filter.SpecifyNoLockHints();
}
//Vùng này chứa các thuộc tính để thao tác với các đối tượng khác
//Hiện tại ko dùng
#region "Public Properties"
#endregion
#region "Private Methods"
#region "Private Methods including Common methods and functions: Initialize,IsValidData, SetControlStatus,..."
/// <summary>
/// Kiểm tra tính hợp lệ của dữ liệu trước khi đóng gói dữ liệu vào Entity
/// </summary>
/// <returns></returns>
private bool IsValidData()
{
errorProvider1.Clear();
if (String.IsNullOrEmpty(txtID.Text))
{
errorProvider1.SetError(txtID, "Bạn cần nhập mã loại đối tượng.");
txtID.Focus();
return(false);
}
SqlQuery q = new Select().From(DmucDoituongkcb.Schema).Where(DmucDoituongkcb.Columns.MaDoituongKcb).IsEqualTo(Utility.DoTrim(txtObjectCode.Text));
if (m_enAction == action.Update)
{
q.And(DmucDoituongkcb.Columns.IdDoituongKcb).IsNotEqualTo(Utility.Int32Dbnull(txtID.Text, -1));
}
if (q.GetRecordCount() > 0)
{
errorProvider1.SetError(txtObjectCode, "Đã tồn tại đối tượng có mã " + Utility.DoTrim(txtObjectCode.Text) + ". Mời bạn nhập mã khác");
txtObjectCode.Focus();
return(false);
}
if (String.IsNullOrEmpty(txtName.Text))
{
errorProvider1.SetError(txtName, "Bạn cần nhập tên loại đối tượng.");
txtName.Focus();
return(false);
}
return(true);
}
/// <summary>
/// Removes contacts from list of <paramref name="contacts"/> if users with this contacts exists.
/// </summary>
/// <param name="userConnection">Instance of the current user connection.</param>
/// <param name="contacts">List of contacts.</param>
/// <param name="ignorePortalUsers">If true, <see cref="SysAdminUnit"/> with SSP ConnectionType selected as contact.
/// </param>
public static void RemoveUsersContacts(UserConnection userConnection, Dictionary <Guid, string> contacts,
bool ignorePortalUsers = false)
{
var queryParameters = contacts.Select(item => new QueryParameter(item.Key)).ToList();
var selectUsers = new Select(userConnection)
.Column("sa", "ContactId")
.From("SysAdminUnit").As("sa")
.InnerJoin("Contact").As("co")
.On("sa", "ContactId").IsEqual("co", "Id")
.Where("sa", "ContactId").In(queryParameters) as Select;
if (ignorePortalUsers)
{
selectUsers = selectUsers.And("sa", "ConnectionType")
.IsNotEqual(Column.Parameter((int)UserType.SSP)) as Select;
}
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection()) {
using (var reader = selectUsers.ExecuteReader(dbExecutor)) {
while (reader.Read())
{
Guid contactId = reader.GetColumnValue <Guid>("ContactId");
contacts.Remove(contactId);
}
}
}
}
private EntitySchemaQuery GetEsqForInitialRecords(string schemaName, int offset, string[] columns)
{
string recordIdColumnName = GetRecordIdColumnName(schemaName);
var esq = new EntitySchemaQuery(_userConnection.EntitySchemaManager, schemaName);
esq.PrimaryQueryColumn.IsAlwaysSelect = true;
_clientColumns = new Dictionary <string, string>();
foreach (var column in columns)
{
_clientColumns[esq.AddColumn(column).Name] = column;
}
if (!_clientColumns.ContainsKey(CreateOnColumnName))
{
_clientColumns[esq.AddColumn(CreateOnColumnName).Name] = CreateOnColumnName;
}
Select esqSelect = esq.GetSelectQuery(_userConnection);
Select initialSelect = CreateInitialResultsSelect(schemaName, offset);
initialSelect.And(initialSelect.SourceExpression.Alias, recordIdColumnName)
.IsEqual(esqSelect.SourceExpression.Alias, "Id");
if (esqSelect.HasCondition)
{
esqSelect.And().Exists(initialSelect);
}
else
{
esqSelect.Where().Exists(initialSelect);
}
return(esq);
}
protected override void AddConditions(Select select)
{
base.AddConditions(select);
select.And("Reminding", "NotificationTypeId")
.IsEqual(Column.Parameter(RemindingConsts.NotificationTypeNotificationId))
.And("Reminding", "SysEntitySchemaId").In(Column.Parameter(GetSysProcessLogUId()));
}
public static void AddLczColumn(UserConnection userConnection, Guid cultureId, string cultureName,
Select select, string schemaName, string schemaAlias, string referencePath, string columnName,
bool useInnerJoin = true, string columnAlias = null, string tableAlias = null)
{
var schema = userConnection.EntitySchemaManager.GetInstanceByName(schemaName);
string lczTableName = schema.GetLocalizableStringsSchemaName();
string lczTableAliasName = string.IsNullOrEmpty(tableAlias)
? GetLczAliasName(schemaName, cultureName)
: tableAlias;
string lczColumnAliasName = string.IsNullOrEmpty(columnAlias)
? GetLczAliasName(string.Concat(schemaName, columnName), cultureName)
: columnAlias;
string lczColumnName = UseMultilanguageData ? columnName : "Value";
IsNullQueryFunction lczColumnQueryFunction = GetLczColumnQueryFunction(lczTableAliasName, lczColumnName,
schemaAlias, columnName);
select.Column(lczColumnQueryFunction).As(lczColumnAliasName);
if (useInnerJoin)
{
select.InnerJoin(lczTableName).As(lczTableAliasName)
.On(lczTableAliasName, "RecordId").IsEqual(schemaAlias, referencePath)
.And(lczTableAliasName, "SysCultureId").IsEqual(new QueryParameter(cultureId));
}
else
{
select.LeftOuterJoin(lczTableName).As(lczTableAliasName)
.On(lczTableAliasName, "RecordId").IsEqual(schemaAlias, referencePath)
.And(lczTableAliasName, "SysCultureId").IsEqual(new QueryParameter(cultureId));
}
if (!UseMultilanguageData)
{
Guid columnUId = schema.Columns.GetByName(columnName).UId;
select.And(lczTableAliasName, "ColumnUId").IsEqual(new QueryParameter(columnUId));
}
}
private void AddSysAdminUnitCondition(Guid?sysAdminUnitId, Select selectQuery)
{
bool isFeatureEnable = _userConnection.GetIsFeatureEnabled("FindDuplicatesOnSave");
if (isFeatureEnable)
{
if (sysAdminUnitId == null)
{
selectQuery.And("SysAdminUnitId").IsNull();
}
else
{
selectQuery.And("SysAdminUnitId").IsEqual(Column.Parameter(sysAdminUnitId));
}
}
}
private void ExtendRecipientsSelectWithFilters(ref Select recipientsSelect, List <Select> filters)
{
if (!filters.Any())
{
return;
}
var filtersSelect = SelectBuilder.Append(filters);
if (filtersSelect != null)
{
if (recipientsSelect.HasCondition)
{
recipientsSelect
.And(SegmentationContext.SourceAliasForFilter, SegmentationContext.SourceColumnForFilter)
.In(filtersSelect);
}
else
{
recipientsSelect
.Where(SegmentationContext.SourceAliasForFilter, SegmentationContext.SourceColumnForFilter)
.In(filtersSelect);
}
recipientsSelect.SpecifyNoLockHints();
}
}
public override Select CreateSelect()
{
var entitySchemaManager = UserConnection.EntitySchemaManager;
var rightsSchemaName = "SysEntitySchemaOperationRight";
//Код, который компилируется только в Workspace CR173389
var select = new Select(Page.UserConnection)
.Column(rightsSchemaName, "Id")
.Column(rightsSchemaName, "SubjectSchemaUId").As("SubjectSchemaId")
.Column("VwSysSchemaInWorkspace", "Caption").As("SubjectSchemaCaption")
.Column(rightsSchemaName, "SysAdminUnitId")
.Column("SysAdminUnit", "Name").As("SysAdminUnitName")
.Column(rightsSchemaName, "CanRead")
.Column(rightsSchemaName, "CanAppend")
.Column(rightsSchemaName, "CanEdit")
.Column(rightsSchemaName, "CanDelete")
.Column(rightsSchemaName, "Position")
.From(rightsSchemaName)
.InnerJoin("SysAdminUnit").On("SysAdminUnit", "Id").IsEqual(rightsSchemaName, "SysAdminUnitId")
.OrderByAsc(rightsSchemaName, "SubjectSchemaUId")
.OrderByAsc(rightsSchemaName, "Position") as Select;
select.InnerJoin("VwSysSchemaInWorkspace").On("VwSysSchemaInWorkspace", "UId").IsEqual(rightsSchemaName, "SubjectSchemaUId");
select.Where("SysEntitySchemaOperationRight", "SysAdminUnitId").IsEqual(Column.Parameter(SelectedNodePrimaryColumnValue));
select.And("VwSysSchemaInWorkspace", "SysWorkspaceId").IsEqual(Column.Parameter(UserConnection.Workspace.Id));
return(select);
}
private Select GetBaseSelect(string anniversarySchemaName)
{
var isAccountAnniversary = IsAccountAnniversary(anniversarySchemaName);
string usingSchemaName = isAccountAnniversary ? "AccountAnniversary" : "Contact";
string column = isAccountAnniversary ? "Date" : "BirthDate";
var targetEntitySchema = GetTargetEntitySchema(anniversarySchemaName);
Select select =
new Select(UserConnection)
.Column(SchemaName, "OwnerId").As("ReceiverId")
.Column(anniversarySchemaName, "Id")
.Column(Column.Parameter(targetEntitySchema.UId)).As("SubjectId")
.Column(Func.CustomFunction("fn_CastDateToString", Column.SourceColumn(usingSchemaName, column)))
.As("SubjectCaption")
.Column(Column.Parameter(DateTime.UtcNow)).As("RemindTime")
.Column(Column.Parameter(RemindingConsts.NotificationTypeAnniversaryId)).As("NotificationTypeId")
.Column(Column.Parameter(0)).As("ProcessListeners")
.Column(SchemaName, "Id").As("SenderId")
.Column(Column.Parameter(SourceId)).As("SourceId")
.From(SchemaName);
if (UserConnection.GetIsFeatureEnabled("NotificationV2"))
{
select = select.Column(anniversarySchemaName, "Name")
.Column(usingSchemaName, column).As("AnniversaryBaseDate");
}
select = AddDefaultAnniversaryFilters(select, anniversarySchemaName);
if (RecordId.IsNotEmpty())
{
select.And(SchemaName, "Id").IsEqual(Column.Parameter(RecordId));
}
return(select);
}
private void ExtendConditionsWithEntityIdFilter(ref Select entityFilterSelect, Guid entityId)
{
entityFilterSelect = (entityFilterSelect.HasCondition
? entityFilterSelect.And(entityFilterSelect.Columns.First()).IsEqual(Column.Parameter(entityId))
: entityFilterSelect.Where(entityFilterSelect.Columns.First()).IsEqual(Column.Parameter(entityId))) as Select;
entityFilterSelect.SpecifyNoLockHints();
}
/// <summary>
/// hàm thực hiện kiểm tra thông tin của phần mã tham gia đối tượng bảo hiểm
/// </summary>
/// <returns></returns>
private bool IsValidData()
{
Utility.SetMsg(lblMsg, "", false);
if (cboLoaiKham.Items.Count <= 0)
{
Utility.SetMsg(lblMsg, "Phải khởi tạo danh mục loại khám trước khi thực hiện chức năng này", true);
cboLoaiKham.Focus();
return(false);
}
if (cboDoituong.Items.Count <= 0)
{
Utility.SetMsg(lblMsg, "Phải khởi tạo danh mục Đối tượng trước khi thực hiện chức năng này", true);
cboDoituong.Focus();
return(false);
}
if (cboDepartment1.Items.Count <= 0)
{
Utility.SetMsg(lblMsg, "Phải khởi tạo danh mục Khoa trước khi thực hiện chức năng này", true);
cboDepartment1.Focus();
return(false);
}
if (cboRoomDept.Items.Count <= 0)
{
Utility.SetMsg(lblMsg,
"Phải khởi tạo danh mục Phòng ban thuộc Khoa " + cboDepartment1.Text +
" trước khi thực hiện chức năng này", true);
cboRoomDept.Focus();
return(false);
}
SqlQuery q =
new Select().From(DmucDichvukcb.Schema).Where(DmucDichvukcb.Columns.MaDichvukcb).IsEqualTo(
Utility.DoTrim(txtCode.Text));
if (m_enAction == action.Update)
{
q.And(DmucDichvukcb.Columns.IdDichvukcb).IsNotEqualTo(Utility.Int32Dbnull(txtInsObject_ID.Text, -1));
}
if (q.GetRecordCount() > 0)
{
Utility.SetMsg(lblMsg,
"Đã tồn tại dịch vụ KCB có mã " + Utility.DoTrim(txtCode.Text) + ". Mời bạn nhập mã khác",
true);
txtCode.Focus();
return(false);
}
//Kiểm tra xem quan hệ này đã có hay chưa
if (blnIsExisted(Id, txtCode.Text.Trim(), Convert.ToInt32(cboDoituong.SelectedValue),
Convert.ToInt32(cboRoomDept.SelectedValue), Convert.ToInt32(cboLoaiKham.SelectedValue),
Convert.ToInt32(cboBacSy.SelectedValue)))
{
Utility.SetMsg(lblMsg,
"Đã tồn tại quan hệ này. Đề nghị bạn chọn các cặp đối tượng khác! " + cboDepartment1.Text +
" trước khi thực hiện chức năng này", true);
cboRoomDept.Focus();
return(false);
}
return(true);
}
protected override void AddConditions(Select select)
{
base.AddConditions(select);
string notificationTypeName = NotificationProviderType.Notification.ToString();
select.And("NotificationType", "Name").IsEqual(Column.Parameter(notificationTypeName))
.And("Reminding", "SysEntitySchemaId").IsEqual(accountSchemaUId);
}
protected override void AddArchivingQueryFilters(Select query)
{
query.Join(JoinType.Inner, JoinSchema).As(JoinSchema)
.On(JoinSchema, KeyColumn)
.IsEqual(SchemaName, JoinColumn);
AddBaseArchivingQueryFilters(query, JoinSchema);
query.And(SchemaName, JoinColumn).In(GetProcessLogIdsSelect());
}
private void SetNotEmployeeFilter(Select select)
{
select.And()
.OpenBlock("Contact", "TypeId")
.IsNotEqual(Column.Parameter(ContactConsts.EmployeeTypeId))
.Or("Contact", "TypeId").IsNull()
.CloseBlock();
}
private void ExtendAudienceSelectWithFilter(ref Select extendedAudienceSelect, Select filter) =>
extendedAudienceSelect = (extendedAudienceSelect.HasCondition
? extendedAudienceSelect
.And(SegmentationContext.SourceAliasForFilter, SegmentationContext.SourceColumnForFilter)
.In(filter)
: extendedAudienceSelect
.Where(SegmentationContext.SourceAliasForFilter, SegmentationContext.SourceColumnForFilter)
.In(filter)) as Select;
private Select AddAccountAnniversaryFilters(Select select)
{
select.Join(JoinType.Inner, "AccountAnniversary")
.On("Account", "Id").IsEqual("AccountAnniversary", "AccountId");
select.And("AccountAnniversary", "AnniversaryTypeId").IsEqual(Column
.Parameter(AnniversaryTypeConsts.FoundationDayId));
return(select);
}
protected void dxcboDestControl_ItemsRequestedByFilterCondition(object source, ListEditItemsRequestedByFilterConditionEventArgs e)
{
ASPxComboBox _combo = (ASPxComboBox)source;
if (_combo != null)
{
string[] _cols = { "EmployeesTable.EmployeeID, EmployeesTable.Name", "EmployeesTable.DepartmentID", "OfficeTable.OfficeID", " NameAndAddressBook.CompanyID" };
string[] _order = { "Name" };
SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn).
InnerJoin(DAL.Logistics.NameAndAddressBook.CountryIDColumn, DAL.Logistics.OfficeTable.CountryIDColumn);
_qry.And(DAL.Logistics.EmployeesTable.LiveColumn).IsEqualTo(true).OrderAsc(_order);
IDataReader _rd1 = _qry.ExecuteReader();
_combo.DataSource = _rd1;
_combo.ValueField = "EmployeeID";
_combo.ValueType = typeof(int);
_combo.TextField = "Name";
_combo.DataBindItems();
}
}
//end incremental filtering agentarorigin
/// <summary>
/// destinatiobn controller
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dxcboDestControl_ItemRequestedByValue(object source, ListEditItemRequestedByValueEventArgs e)
{
ASPxComboBox _combo = (ASPxComboBox)source;
ASPxComboBox _destinationagent = (ASPxComboBox)this.fmvTemplate.FindControl("dxcboAgentAtDestinationIDEdit");
if (_combo != null)
{
string[] _cols = { "EmployeesTable.EmployeeID, EmployeesTable.Name", "EmployeesTable.DepartmentID", "OfficeTable.OfficeID", " NameAndAddressBook.CompanyID" };
string[] _order = { "Name" };
SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn).
InnerJoin(DAL.Logistics.NameAndAddressBook.CountryIDColumn, DAL.Logistics.OfficeTable.CountryIDColumn);
if (_destinationagent != null && _destinationagent.SelectedItem != null && _destinationagent.Value != null)
{
int _filter = wwi_func.vint(_destinationagent.SelectedItem.GetValue("CountryID").ToString());
if (_filter > 0) { _qry.Where("CountryID").IsEqualTo(_filter); }
}
_qry.And(DAL.Logistics.EmployeesTable.LiveColumn).IsEqualTo(true).OrderAsc(_order);
IDataReader _rd1 = _qry.ExecuteReader();
_combo.DataSource = _rd1;
_combo.ValueField = "EmployeeID";
_combo.ValueType = typeof(int);
_combo.TextField = "Name";
_combo.DataBindItems();
}
}
//end bind origin port controller
protected void bind_destination_controller(string destinationAgentID)
{
ASPxComboBox _combo = (ASPxComboBox)this.fmvTemplate.FindControl("dxcboDestinationPortControllerID");
if (_combo != null)
{
string[] _cols = { "EmployeesTable.EmployeeID, EmployeesTable.Name", "EmployeesTable.DepartmentID", "OfficeTable.OfficeID"};
string[] _order = { "Name" };
SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn);
if (!string.IsNullOrEmpty(destinationAgentID))
{
int _filter = wwi_func.vint(destinationAgentID);
_qry.Where("OfficeID").IsEqualTo(_filter);
}
_qry.And(DAL.Logistics.EmployeesTable.LiveColumn).IsEqualTo(true).OrderAsc(_order);
DataTable _dt = _qry.ExecuteDataSet().Tables[0];
IDataReader _rd1 = _qry.ExecuteReader();
_combo.DataSource = _rd1;
_combo.ValueField = "EmployeeID";
_combo.ValueType = typeof(int);
_combo.TextField = "Name";
_combo.DataBindItems();
}
}
protected void bind_origin_controller(string originAgentID)
{
//260211 some older jobs have an origin controller but no origin agent in those cases don't display the origin controller
//must have a filter or display nothing
if (!string.IsNullOrEmpty(originAgentID))
{
ASPxComboBox _dxcboController = (ASPxComboBox)this.fmvTemplate.FindControl("dxcboOriginPortControllerID");
if (_dxcboController != null)
{
string[] _cols = { "EmployeesTable.EmployeeID, EmployeesTable.Name", "EmployeesTable.DepartmentID", "OfficeTable.OfficeID", " NameAndAddressBook.CompanyID" };
string[] _order = { "Name" };
SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn).
InnerJoin(DAL.Logistics.NameAndAddressBook.CountryIDColumn, DAL.Logistics.OfficeTable.CountryIDColumn);
if (!string.IsNullOrEmpty(originAgentID))
{
int _filter = wwi_func.vint(originAgentID);
if (_filter > 0) { _qry.Where("CompanyID").IsEqualTo(_filter); }
}
_qry.And(DAL.Logistics.EmployeesTable.LiveColumn).IsEqualTo(true).OrderAsc(_order);
IDataReader _rd1 = _qry.ExecuteReader();
_dxcboController.DataSource = _rd1;
_dxcboController.ValueField = "EmployeeID";
_dxcboController.ValueType = typeof(int);
_dxcboController.TextField = "Name";
_dxcboController.DataBindItems();
}
}
}
public static DataTable Laydanhmuckhoa(string NoitruNgoaitru, int PhongChucnang)
{
try
{
SqlQuery sqlQuery = new Select().From(VDmucKhoaphong.Schema).Where(VDmucKhoaphong.Columns.KieuKhoaphong).IsEqualTo("KHOA");
if (PhongChucnang > -1)
sqlQuery.And(VDmucKhoaphong.Columns.PhongChucnang).IsEqualTo(PhongChucnang);
if (NoitruNgoaitru != "ALL")
sqlQuery.And(VDmucKhoaphong.Columns.NoitruNgoaitru).IsEqualTo(NoitruNgoaitru);
return sqlQuery.ExecuteDataSet().Tables[0];
}
catch
{
return null;
}
}
protected void bind_destination_controller(int countryId)
{
ASPxComboBox _combo = (ASPxComboBox)this.fmvAddresses.FindControl("dxcboDestinationPortControllerIDEdit");
if (_combo != null)
{
string[] _cols = { "EmployeesTable.EmployeeID, EmployeesTable.Name", "EmployeesTable.DepartmentID", "OfficeTable.OfficeID"};
string[] _order = { "Name" };
//don't need nameandaddressbook as get countryid from officetable
//SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
// InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn).
// InnerJoin(DAL.Logistics.NameAndAddressBook.CountryIDColumn, DAL.Logistics.OfficeTable.CountryIDColumn);
SqlQuery _qry = new Select(_cols).From(DAL.Logistics.Tables.EmployeesTable).
InnerJoin(DAL.Logistics.OfficeTable.OfficeIDColumn, DAL.Logistics.EmployeesTable.OfficeIDColumn);
if (countryId > 0)
{
_qry.Where("CountryID").IsEqualTo(countryId);
}
_qry.And(DAL.Logistics.EmployeesTable.LiveColumn).IsEqualTo(1).OrderAsc(_order);
//string _q = _qry.ToString(); //fo testing
//DataTable _dt = _qry.ExecuteDataSet().Tables[0]; //for testing
IDataReader _rd1 = _qry.ExecuteReader();
_combo.DataSource = _rd1;
_combo.ValueField = "EmployeeID";
_combo.ValueType = typeof(int);
_combo.TextField = "Name";
_combo.DataBindItems();
}
}
