public string GetPublicIp()
{
var ipServices = new Func<string>[]
{
() => new IpInfoIpService().GetPublicIp(),
() => new DynDnsIpService().GetPublicIp(),
() => new IpifyIpService().GetPublicIp(),
() => new ICanHazIpService().GetPublicIp(),
() => new FreeGeoIpService().GetPublicIp()
};
for (int i = 0; i < ipServices.Count(); i++)
{
if (lastUsedIndex >= ipServices.Count()) lastUsedIndex = 0;
else lastUsedIndex++;
try
{
return ipServices[lastUsedIndex]();
}
catch { }
}
return null;
}
private Select GetParticipantsAnalyticsSelect(ParticipantsAnalyticsRequest analyticsRequest)
{
var select = new Select(UserConnection)
.Column("cp", "CampaignItemId")
.Column("cp", "StepCompleted")
.Column("cp", "StatusId")
.Column(Func.Count("cp", "CampaignItemId")).As("Count")
.From("CampaignParticipant").As("cp")
.Where("cp", "CampaignId").IsEqual(Column.Parameter(analyticsRequest.CampaignId))
.GroupBy("cp", "CampaignItemId")
.GroupBy("cp", "StatusId")
.GroupBy("cp", "StepCompleted") as Select;
select.SpecifyNoLockHints();
if (analyticsRequest.UseTimeFilters)
{
var filterStartDateCondition = select.AddCondition(select.SourceExpression.Alias, "CreatedOn",
LogicalOperation.And);
filterStartDateCondition.IsGreaterOrEqual(Column.Parameter(analyticsRequest.FilterStartDate));
var filterDueDateCondition = select.AddCondition(select.SourceExpression.Alias, "CreatedOn",
LogicalOperation.And);
filterDueDateCondition.IsLessOrEqual(Column.Parameter(analyticsRequest.FilterDueDate));
}
return(select);
}
/// <summary>
/// Calculate completeness value from detail.
/// </summary>
/// <param name="param">Completeness parameter for detail completeness.</param>
/// <param name="recordId">Record id.</param>
/// <returns>Completeness value.</returns>
protected int GetCompletenessFromDetail(CompletenessParameter param, Guid recordId)
{
int detailCompleteness = 0;
Select entitiesSelect =
new Select(AppConnection.SystemUserConnection)
.Column(Func.Count(param.DetailColumn))
.From(param.DetailEntityName)
.Where(param.DetailColumn).IsEqual(new QueryParameter(recordId)) as Select;
if (!string.IsNullOrEmpty(param.TypeColumn) && !param.TypeValue.IsEmpty())
{
var typeCondition = new QueryCondition {
LeftExpression = new QueryColumnExpression(param.TypeColumn)
};
typeCondition.In(new QueryParameter(param.TypeValue));
entitiesSelect.AddCondition(typeCondition, LogicalOperation.And);
}
entitiesSelect.SpecifyNoLockHints();
using (DBExecutor dbExecutor = EnsureSystemUserDbConnection()) {
using (IDataReader dataReader = entitiesSelect.ExecuteReader(dbExecutor)) {
while (dataReader.Read())
{
if (Convert.ToInt32(dataReader.GetValue(0)) > 0)
{
detailCompleteness = param.Percentage;
}
}
}
}
return(detailCompleteness);
}
/// <summary>
/// Creates <see cref="Workplace"/> data select.
/// </summary>
/// <returns><see cref="Select"/> instance.</returns>
private Select GetWorkplacesSelect()
{
var typeQuery = new Select(_userConnection)
.Column(Func.Count("sm", "Id"))
.From("SysModule").As("sm")
.InnerJoin("SysModuleEntity").As("sme").On("sme", "Id").IsEqual("sm", "SysModuleEntityId")
.InnerJoin("SysModuleInWorkplace").As("smiw").On("sm", "Id").IsEqual("smiw", "SysModuleId")
.InnerJoin("SysModuleEntityInPortal").As("smeip").On("sme", "Id").IsEqual("smeip", "SysModuleEntityId")
.Where("smiw", "SysWorkplaceId").IsEqual("sw", "Id");
var workplaceSelect = new Select(_userConnection)
.Column("sw", "Id")
.Column("sw", "Position")
.Column("sw", "Name")
.Column("sw", "IsPersonal")
.Column("sw", "LoaderId")
.Column("sw", "SysApplicationClientTypeId")
.Column(GetClientUnitSchemaNameSelect("sw", "LoaderId")).As("LoaderName")
.From("SysWorkplace").As("sw")
.LeftOuterJoin("SysWorkplaceType").As("swt").On("sw", "TypeId").IsEqual("swt", "Id") as Select;
if (GetIsFeatureEnabled("UseTypedWorkplaces"))
{
(workplaceSelect).Column("swt", "Code").As("Type");
}
else
{
workplaceSelect.Column(typeQuery).As("Type");
}
return(workplaceSelect);
}
private Select GetSubselectForCityCount()
{
return(new Select(UserConnection)
.Column(Func.Count("Id"))
.From("City").As("cityCount")
.Where("cityCount", "Name").IsEqual("City", "Name") as Select);
}
/// <summary>
/// Get count of actual cases.
/// </summary>
public new int GetCount()
{
int result = 0;
Guid sysAdminUnitId;
if (!Guid.TryParse(parameters["sysAdminUnitId"].ToString(), out sysAdminUnitId))
{
return(result);
}
DateTime date;
if (!DateTime.TryParse(parameters["dueDate"].ToString(), out date))
{
return(result);
}
var countSelect = new Select(UserConnection)
.Column(Func.Count("r", "Id"))
.Distinct()
.From("Reminding").As("r")
.LeftOuterJoin("SysAdminUnit").As("sau")
.On("sau", "ContactId").IsEqual("r", "ContactId")
.InnerJoin("Case").As("case")
.On("case", "Id").IsEqual("r", "SubjectId")
.Where("r", "RemindTime").IsLessOrEqual(Column.Const(date))
.And("r", "IsRead").IsEqual(Column.Parameter(false))
.And("sau", "Id").IsEqual(Column.Parameter(sysAdminUnitId)) as Select;
result = countSelect.ExecuteScalar <int>();
return(result);
}
/// <summary>
/// Returns query for count of sent messages of bulk email.
/// </summary>
/// <param name="bulkEmailRId">RId of bulk email.</param>
/// <param name="userConnection">User connection instance.</param>
/// <returns>Query for count of sent messages of bulk email.</returns>
private static Select GetBulkEmailSentMessageCountSelect(int bulkEmailRId, UserConnection userConnection)
{
return(new Select(userConnection)
.Column(Func.Count("Id"))
.From("MandrillRecipient")
.Where("IsSent").IsEqual(Column.Const(true))
.And("BulkEmailRId").IsEqual(Column.Parameter(bulkEmailRId)) as Select);
}
private bool IsRecipientsSelectQueryEmpty(Select select)
{
var execSelect = new Select(UserConnection).Column(Func.Count("RecipientUId")).From(select.Top(1))
.As("IsRecipientsSelectQueryEmpty");
var bulkEmailAudienceCount = execSelect.ExecuteScalar <int>();
return(bulkEmailAudienceCount == 0);
}
private static int CountOverlappingActivitiesSelect(Entity activity)
{
int result = 0;
UserConnection userConnection = activity.UserConnection;
if (activity.GetTypedColumnValue <Guid>("TypeId") != activityType)
{
return(result);
}
if (activity.GetTypedColumnValue <Guid>("ActivityCategoryId") != activityCategory)
{
return(result);
}
if (activity.GetTypedColumnValue <Guid>("StatusId") != activityStatusInProgress &&
activity.GetTypedColumnValue <Guid>("StatusId") != activityStatusNotStarted)
{
return(result);
}
Guid activityId = activity.GetTypedColumnValue <Guid>("Id");
Guid ownerId = activity.GetTypedColumnValue <Guid>("OwnerId");
Select select = new Select(userConnection)
.Column(Func.Count("Id"))
.From("Activity")
.Where("TypeId").IsEqual(Column.Parameter(activityType))
.And("ActivityCategoryId").IsEqual(Column.Parameter(activityCategory))
.And("OwnerId").IsEqual(Column.Parameter(ownerId))
.And("Id").IsNotEqual(Column.Parameter(activityId))
as Select;
TimeZoneInfo userTimeZoneInfo = userConnection.CurrentUser.TimeZone;
DateTime start = TimeZoneInfo.ConvertTimeToUtc(activity.GetTypedColumnValue <DateTime>("StartDate"), userTimeZoneInfo);
DateTime due = TimeZoneInfo.ConvertTimeToUtc(activity.GetTypedColumnValue <DateTime>("DueDate"), userTimeZoneInfo);
select.And()
//Case 1: Search target starts during new activity
.OpenBlock("StartDate").IsBetween(Column.Parameter(start)).And(Column.Parameter(due))
//Case2: Search target ends during new Activity
.Or("DueDate").IsBetween(Column.Parameter(start)).And(Column.Parameter(due))
//Case 3: activity inside search target;
.Or()
.OpenBlock("StartDate").IsLessOrEqual(Column.Parameter(start))
.And("DueDate").IsGreaterOrEqual(Column.Parameter(due))
.CloseBlock();
select.BuildParametersAsValue = true;
_log.Info(select.GetSqlText());
result = select.ExecuteScalar <int>();
return(result);
}
/// <inheritdoc cref="IDuplicatesScheduledSearchParameterRepository.GetIsSearchParametersExist"/>
public bool GetIsSearchParametersExist(string schemaName)
{
var selectQuery = new Select(_userConnection)
.Column(Func.Count("SchemaToSearchName"))
.From(DuplicatesSearchParameterSchemaName)
.Where(DuplicatesSearchParameterSchemaName, "SchemaToSearchName").IsEqual(Column.Parameter(schemaName));
return(((Select)selectQuery).ExecuteScalar <bool>());
}
private Query GetCheckLicQuery(string apiKey)
{
var select = new Select(_userConnection)
.Column(Func.Count("Id")).As("Count")
.From("SysAzureCogInfo")
.Where("ApiKey").IsEqual(Column.Const(apiKey));
select.InitializeParameters();
return(select);
}
/// <summary>
/// Returns address count.
/// </summary>
/// <returns>Primary count.</returns>
public virtual int GetAddressesCount()
{
var masterEntityId = AddressEntity.GetTypedColumnValue <Guid>(AddressReferenceColumnName);
Select primaryCountSelect = new Select(UserConnection)
.Column(Func.Count("Id"))
.From(AddressEntityName)
.Where(AddressReferenceColumnName).IsEqual(Column.Parameter(masterEntityId)) as Select;
return(primaryCountSelect.ExecuteScalar <int>());
}
/// <summary>
/// Returns query for count of recipients in queue of bulk email.
/// </summary>
/// <param name="bulkEmailId">Unique id of bulk email.</param>
/// <param name="userConnection">User connection instance.</param>
/// <returns>Query for count of recipients in queue of bulk email.</returns>
private static Select GetRecipientsInQueueCountSelect(Guid bulkEmailId, UserConnection userConnection)
{
var countSelect = new Select(userConnection)
.Column(Func.Count("Id"))
.From("VwMandrillRecipient")
.Where("BulkEmailId").IsEqual(Column.Parameter(bulkEmailId)) as Select;
HintsHelper.SpecifyNoLockHints(countSelect, true);
return(countSelect);
}
private Select GetEventTotalCountSelect()
{
var select = new Select(UserConnection)
.Column(Func.Count("Id"))
.From(EventTargetTableName)
.Where("EventId").IsEqual(Column.Parameter(EventId)) as Select;
select.SpecifyNoLockHints();
return(select);
}
/// <summary>
/// Check if audience is empty.
/// </summary>
/// <returns>True if audience is empty.</returns>
public bool IsEmpty()
{
var recipientsBatchSelect = GetRecipientsBatchSelect(false);
var execSelect = new Select(_userConnection)
.Column(Func.Count("ContactRId"))
.From(recipientsBatchSelect.Top(1)).As("Select");
var bulkEmailAudienceCount = execSelect.ExecuteScalar <int>();
return(bulkEmailAudienceCount == 0);
}
private Select GetTotalCampaignParticipantsCountSelect()
{
var select = new Select(UserConnection)
.Column(Func.Count("Id"))
.From(CampaignParticipantTableName)
.Where("CampaignId").IsEqual(Column.Parameter(CampaignSchema.EntityId)) as Select;
select.SpecifyNoLockHints();
return(select);
}
protected virtual Select GetCountSelect(Guid contactId)
{
var select = (Select) new Select(_userConnection)
.Column(Func.Count(Column.Asterisk())).As("Count")
.From("ESNNotification")
.Where("OwnerId").IsEqual(Column.Parameter(contactId))
.And("IsRead").IsEqual(Column.Const(false))
.And("CreatedById").IsNotEqual(Column.Parameter(contactId));
return(select);
}
/// <summary>
/// Checks if bulk email contains an empty template
/// </summary>
/// <param name="bulkEmailIds">array of email's ids</param>
private bool IsEmptyTemplateExist(Guid[] bulkEmailIds)
{
var selectCount = new Select(UserConnection)
.Column(Func.Count("Id"))
.From("BulkEmail")
.Where("Id").In(Column.Parameters(bulkEmailIds))
.And("TemplateBody").IsEqual(Column.Parameter(string.Empty)) as Select;
int count = selectCount.ExecuteScalar <int>();
return(count > 0);
}
/// <summary>
/// Return number of notification.
/// </summary>
/// <returns>A number of notification.</returns>
public virtual int GetCount()
{
Select entitySelect = GetEntitiesSelect();
Select countSelect = new Select(UserConnection)
.Column(Func.Count("Id"))
.Distinct()
.From(entitySelect).As("CountSelect") as Select;
int result = countSelect.ExecuteScalar <int>();
return(result);
}
public static bool DoesExist <Entity>(this Entity entity, string RootSchemName, object searchValue, string searchColumn)
where Entity : Terrasoft.Core.Entities.Entity
{
Select select = new Select(entity.UserConnection)
.Column(Func.Count("Id"))
.From(RootSchemName)
.Where(searchColumn).IsEqual(Column.Parameter(searchValue)) as Select;
int count = select.ExecuteScalar <int>();
return((count == 0) ? false : true);
}
/// <summary>
/// Returns a query to select the number of queue items.
/// </summary>
/// <param name="queryParameters">Query parameters.</param>
/// <returns>Instance of the <see cref="Select"/> type.</returns>
private Select GetQueueCountItemSelect(IEnumerable <Guid> queryParameters)
{
return((Select) new Select(_userConnection).WithHints(Hints.NoLock)
.Column("Q", "Id").As("QueueId")
.Column(Func.Count("QueueId")).As("QueueItemsCount")
.From("QueueItem").As("QI")
.RightOuterJoin("Queue").As("Q")
.On("Q", "Id").IsEqual("QueueId")
.Where("Q", "Id").In(Column.Parameters(queryParameters))
.GroupBy("Q", "Id"));
}
/// <summary>
/// Verifies the existence of the contact synchronization settings with current email.
/// </summary>
/// <param name="userConnection">A instance of the current user connection.</param>
/// <param name="contactId">Id of the contact.</param>
/// <param name="email">Email.</param>
/// <returns>Flag existence synchronization settings with current email.
/// </returns>
public static bool IsSyncContactExist(UserConnection userConnection, Guid contactId, string email)
{
Select SelectAppointment = new Select(userConnection)
.Column(Func.Count("sa", "ContactId"))
.From("MailboxSyncSettings").As("ms")
.InnerJoin("SysAdminUnit").As("sa")
.On("sa", "Id").IsEqual("ms", "SysAdminUnitId")
.Where("sa", "ContactId").IsEqual(Column.Parameter(contactId))
.And("ms", "SenderEmailAddress").IsEqual(Column.Parameter(email)) as Select;
return(SelectAppointment.ExecuteScalar <int>() > 0);
}
/// <summary>
/// ####### ######### ### ######### ########## ########,
/// ######### #############.
/// </summary>
/// <returns>######### ### ######### ########## ######### ########.</returns>
private Select GenerateSelectForCase()
{
return
(new Select(UserConnection)
.Column(Func.Count("SysLicUser", "Id"))
.From("SysLicUser")
.InnerJoin("SysAdminUnit")
.On("SysAdminUnit", "Id").IsEqual("SysLicUser", "SysUserId")
.Where("lic", "SysLicPackageId").IsEqual("SysLicPackageId")
.And("SysLicUser", "Active").IsEqual(Column.Parameter(true))
.And("SysAdminUnit", "Active").IsEqual(Column.Parameter(true)) as Select);
}
/// <summary>
/// Creates query to get contacts in current email for whom communication is restricted by given rule.
/// Override this method to get custom query for restricted contacts.
/// </summary>
/// <param name="rule">Communication limit rule. Instance of <see cref="BulkEmailCountLimit"/>.</param>
/// <param name="bulkEmailRId">Email identifier (RId field in BulkEmail).</param>
/// <returns>Instance of <see cref="Select"/>. It should select single column Contact.Id.</returns>
protected virtual Select GetContactSelectQuery(BulkEmailCountLimit rule, int bulkEmailRId)
{
var betSelect = GetBulkEmailTargetSelectQuery(rule, bulkEmailRId);
var mmrSelect = GetMessageResponseSelectQuery(rule, bulkEmailRId);
var rootSelect = new Select(_userConnection)
.Column("ContactId")
.From(betSelect.UnionAll(mmrSelect)).As("src")
.GroupBy("ContactId")
.Having(Func.Count("src", "ContactId")).IsGreaterOrEqual(Column.Const(rule.MailingLimitCount)) as Select;
return(rootSelect);
}
/// <summary>
/// Inititalezes unique exchange contact id property.
/// </summary>
/// <param name="context"><see cref="SyncContext"/> instance.</param>
/// <param name="useSimpleId">If true, uses <see cref="ExchangeContact.GetItemSimpleId"/> method result
/// as search value.</param>
public void InitIdProperty(SyncContext context, bool useSimpleId = false)
{
string extendedId = GetExtendedId();
string itemId = GetItemSimpleId();
string searchId = useSimpleId ? itemId : extendedId;
Select select = new Select(context.UserConnection)
.Column(Func.Count("Id"))
.From("SysSyncMetaData")
.Where("RemoteId").IsEqual(Column.Parameter(searchId)) as Select;
int count = select.ExecuteScalar <int>();
Id = count > 0 ? extendedId : itemId;
}
/// <summary>
/// 计算进度
/// </summary>
/// <returns></returns>
public float Calculate()
{
if (State == null)
{
return(0);
}
if (State.StudiedChapterIds == null || State.StudiedChapterIds.Length < 1)
{
return(0);
}
if (State.CourseChapters == null || State.CourseChapters.Count() < 1)
{
return(0);
}
//筛选出状态正常的课程
var data = from p in State.CourseChapters
where p.Key.Status == (int)CourseStatus.ENABLED
select p.Value;
//筛选出状态正常的课程下的有效章节
var chapters = new Func <List <long> >(() =>
{
List <long> chapterIds = new List <long>();
foreach (var cha in data)
{
if (cha != null)
{
chapterIds.AddRange(cha.Where(p => p.Status == (int)ChapterStatus.ENABLED).Select(p => p.ChapterId));
}
}
return(chapterIds);
}).Invoke();
//用户学习过的章节
var studiedChapterIds = State.StudiedChapterIds.Where(p => chapters.Contains(p));
float total = chapters.Count();
float studied = studiedChapterIds != null?studiedChapterIds.Count() : 0;
if (total > 0)
{
return((float)Math.Round(studied / total, 2, MidpointRounding.ToEven));
}
else
{
return(0F);
}
}
public virtual bool IsUniquePortalEmailMessage(Entity activity)
{
var select = new Select(activity.UserConnection)
.Column(Func.Count("PortalEmailMessage", "Id"))
.From("PortalEmailMessage")
.Join(JoinType.Inner, "CaseMessageHistory")
.On("PortalEmailMessage", "CaseMessageHistoryId")
.IsEqual("CaseMessageHistory", "Id")
.Where("CaseMessageHistory", "RecordId")
.IsEqual(Column.Parameter(activity.PrimaryColumnValue)) as Select;
var count = select.ExecuteScalar <int>();
return(count == 0);
}
/// <summary>
/// Returns select query for count of active contacts.
/// </summary>
/// <param name="selectCurrentBulkEmailRecipients">Select query for recipients of current bulk email.</param>
/// <param name="selectHistoryBulkEmailsRecipients">Select query for recipients of finished bulk email.</param>
/// <param name="selectNonHistoryBulkEmailsRecipients">Select query for recipients of non-finished emails.</param>
/// <param name="userConnection">User connection instance.</param>
/// <returns>Select query for count of active contacts.</returns>
private static Select GetActiveContactsCountSelect(Select selectCurrentBulkEmailRecipients,
Select selectHistoryBulkEmailsRecipients, Select selectNonHistoryBulkEmailsRecipients,
UserConnection userConnection)
{
var selectCount = new Select(userConnection)
.Column(Func.Count("ContactId"))
.From(new Select(userConnection)
.Column("main", "ContactId")
.From(selectCurrentBulkEmailRecipients.UnionAll(selectNonHistoryBulkEmailsRecipients)
.UnionAll(selectHistoryBulkEmailsRecipients) as Select).As("main")
.GroupBy("main", "ContactId")).As("t");
return(selectCount);
}
/// <summary>
/// Get Select for calculation Account CertifiedContacts count.
/// </summary>
/// <param name="userConnection">UserConnection.</param>
/// <returns> Select for calculation Account CertifiedContacts count.</returns>
private Select GetCertifiedContactsQuery(UserConnection userConnection)
{
var query = new Select(userConnection)
.Column("Contact", "AccountId")
.Column(Func.Count(existsOneConstant)).As("Count")
.From("Contact").Where()
.Exists(new Select(userConnection).Column(existsOneConstant).From("Certificate")
.Where("Certificate", "ContactId").IsEqual("Contact", "Id")
.And("Certificate", "IssueDate").IsLessOrEqual(Column.Parameter(DateTime.UtcNow))
.And("Certificate", "ExpireDate").IsGreaterOrEqual(Column.Parameter(DateTime.UtcNow)))
.GroupBy("Contact", "AccountId");
return(query as Select);
}
private bool HasBulkEmailsWithIncorrectStatus(Guid splitTestId)
{
var selectCount = new Select(_userConnection)
.Column(Func.Count("Id"))
.From("BulkEmail")
.Where("SplitTestId").IsEqual(Column.Parameter(splitTestId)).And()
.OpenBlock("StatusId").IsNotEqual(Column.Parameter(MarketingConsts.BulkEmailStatusPlannedId))
.And("StatusId").IsNotEqual(Column.Parameter(MarketingConsts.BulkEmailStatusStartPlanedId))
.CloseBlock() as Select;
selectCount.SpecifyNoLockHints();
var count = selectCount.ExecuteScalar <int>();
return(count > 0);
}
public int GetCount()
{
Guid sysAdminUnitId = (Guid)this.parameters["sysAdminUnitId"];
var countSelect = new Select(_userConnection).Column(Func.Count("VwVisa", "Id")).Distinct()
.From("VwVisa")
.Where("VisaOwnerId").IsEqual(Column.Parameter(sysAdminUnitId))
.And("IsCanceled").IsEqual(Column.Parameter(false))
.And("StatusId").In(
new Select(_userConnection).Column("Id").From("VisaStatus")
.Where("IsFinal").IsEqual(Column.Parameter(false))
) as Select;
int result = countSelect.ExecuteScalar <int>();
return(result);
}
