private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (filter.MyProjects || filter.MyMilestones)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("t.project_id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (ProjectsCommonSettings.Load().HideEntitiesInPausedProjects)
{
if (!checkAccess && isAdmin)
{
query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.tenant_id", "p.tenant_id") & Exp.EqColumns("t.project_id", "p.id"));
}
query.Where(!Exp.Eq("p.status", ProjectStatus.Paused));
}
}
if (filter.Milestone.HasValue || filter.MyMilestones)
{
query.InnerJoin(MilestonesTable + " pm", Exp.EqColumns("pm.tenant_id", "t.tenant_id") & Exp.EqColumns("pm.project_id", "t.project_id"));
query.Where(Exp.EqColumns("pt.milestone_id", "pm.id"));
if (filter.Milestone.HasValue)
{
query.Where("pm.id", filter.Milestone);
}
else if (filter.MyMilestones)
{
query.Where(Exp.Gt("pm.id", 0));
}
}
if (filter.TagId != 0)
{
if (filter.TagId == -1)
{
query.LeftOuterJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", null);
}
else
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
}
if (filter.UserId != Guid.Empty)
{
query.Where("t.person_id", filter.UserId);
}
if (filter.DepartmentId != Guid.Empty)
{
query.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "t.person_id") & Exp.EqColumns("cug.tenant", "t.tenant_id"));
query.Where("cug.groupid", filter.DepartmentId);
}
var minDate = DateTime.MinValue;
var maxDate = DateTime.MaxValue;
if (!filter.FromDate.Equals(minDate) && !filter.FromDate.Equals(maxDate) &&
!filter.ToDate.Equals(minDate) && !filter.ToDate.Equals(maxDate))
{
query.Where(Exp.Between("t.date", filter.FromDate, filter.ToDate));
}
if (filter.PaymentStatuses.Any())
{
query.Where(Exp.In("payment_status", filter.PaymentStatuses));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.note", filter.SearchText, SqlLike.AnyWhere));
}
if (checkAccess)
{
query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("p.tenant_id", "t.tenant_id") & Exp.EqColumns("p.id", "t.project_id"));
query.Where(Exp.Eq("p.private", false));
}
else if (!isAdmin)
{
query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("p.tenant_id", "t.tenant_id") & Exp.EqColumns("p.id", "t.project_id"));
if (!(filter.MyProjects || filter.MyMilestones))
{
query.LeftOuterJoin(ParticipantTable + " ppp", Exp.Eq("ppp.participant_id", CurrentUserID) & Exp.EqColumns("ppp.project_id", "t.project_id") & Exp.EqColumns("ppp.tenant", "t.tenant_id"));
}
var isInTeam = !Exp.Eq("ppp.security", null) & Exp.Eq("ppp.removed", false);
var canReadTasks = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Tasks, (int)ProjectTeamSecurity.Tasks);
var canReadMilestones = Exp.Eq("pt.milestone_id", 0) | !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone);
var responsible = Exp.Exists(new SqlQuery("projects_tasks_responsible ptr")
.Select("ptr.responsible_id")
.Where(Exp.EqColumns("pt.id", "ptr.task_id") &
Exp.EqColumns("ptr.tenant_id", "pt.tenant_id") &
Exp.Eq("ptr.responsible_id", CurrentUserID)));
query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadTasks & canReadMilestones));
}
query.GroupBy("t.id");
return(query);
}
public int GetDealsCount(String searchText,
Guid responsibleID,
int milestoneID,
IEnumerable <String> tags,
int contactID,
DealMilestoneStatus?stageType,
bool?contactAlsoIsParticipant,
DateTime fromDate,
DateTime toDate)
{
var cacheKey = TenantID.ToString() +
"deals" +
SecurityContext.CurrentAccount.ID.ToString() +
searchText +
responsibleID +
milestoneID +
contactID;
if (tags != null)
{
cacheKey += String.Join("", tags.ToArray());
}
if (stageType.HasValue)
{
cacheKey += stageType.Value;
}
if (contactAlsoIsParticipant.HasValue)
{
cacheKey += contactAlsoIsParticipant.Value;
}
if (fromDate != DateTime.MinValue)
{
cacheKey += fromDate.ToString();
}
if (toDate != DateTime.MinValue)
{
cacheKey += toDate.ToString();
}
var fromCache = _cache.Get(cacheKey);
if (fromCache != null)
{
return(Convert.ToInt32(fromCache));
}
var withParams = !(String.IsNullOrEmpty(searchText) &&
responsibleID == Guid.Empty &&
milestoneID <= 0 &&
(tags == null || !tags.Any()) &&
contactID <= 0 &&
stageType == null &&
contactAlsoIsParticipant == null &&
fromDate == DateTime.MinValue &&
toDate == DateTime.MinValue);
ICollection <int> exceptIDs = CRMSecurity.GetPrivateItems(typeof(Deal)).ToList();
int result;
using (var db = GetDb())
{
if (withParams)
{
var whereConditional = WhereConditional(exceptIDs, searchText, responsibleID, milestoneID, tags,
contactID, stageType, contactAlsoIsParticipant);
var sqlQuery = GetDealSqlQuery(whereConditional);
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
{
sqlQuery.Having(Exp.Between("close_date", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate)));
result = db.ExecuteList(sqlQuery).Count;
}
else if (whereConditional == null)
{
result = 0;
}
else
{
result = db.ExecuteList(sqlQuery).Count;
}
}
else
{
var countWithoutPrivate = db.ExecuteScalar <int>(Query("crm_deal").SelectCount());
var privateCount = exceptIDs.Count;
if (privateCount > countWithoutPrivate)
{
_log.Error("Private deals count more than all deals");
privateCount = 0;
}
result = countWithoutPrivate - privateCount;
}
}
if (result > 0)
{
_cache.Insert(cacheKey, result, new CacheDependency(null, new[] { _dealCacheKey }), Cache.NoAbsoluteExpiration,
TimeSpan.FromSeconds(30));
}
return(result);
}
public static IEnumerable <int> GetChangedTenants(DateTime from, DateTime to)
{
using (var db = GetDbManager())
{
var q = new SqlQuery("webstudio_useractivity").Select("tenantid").Where(Exp.Between("activitydate", from, to)).GroupBy(1);
return(db.ExecuteList(q)
.ConvertAll(r => Convert.ToInt32(r[0])));
}
}
public override IEnumerable <Tuple <Feed, object> > GetFeeds(FeedFilter filter)
{
var query =
new SqlQuery("projects_tasks t")
.Select(TaskColumns().Select(t => "t." + t).ToArray())
.LeftOuterJoin("projects_tasks_responsible r", Exp.EqColumns("r.task_id", "t.id") & Exp.Eq("r.tenant_id", filter.Tenant))
.Select("group_concat(distinct r.responsible_id)")
.InnerJoin("projects_projects p", Exp.EqColumns("p.id", "t.project_id") & Exp.Eq("p.tenant_id", filter.Tenant))
.Select(ProjectColumns().Select(p => "p." + p).ToArray())
.LeftOuterJoin("projects_comments c", Exp.EqColumns("c.target_uniq_id", "concat('Task_', t.id)") & Exp.Eq("c.tenant_id", filter.Tenant) & Exp.Eq("c.inactive", 0))
.Select(CommentColumns().Select(c => "c." + c).ToArray())
.Where("t.tenant_id", filter.Tenant)
.Where(Exp.Between("t.create_on", filter.Time.From, filter.Time.To) | Exp.Between("c.create_on", filter.Time.From, filter.Time.To))
.GroupBy("c.id, t.id");
using (var db = new DbManager(DbId))
{
var comments = db.ExecuteList(query)
.ConvertAll(ToComment);
var groupedTasks = comments.GroupBy(c => c.Task.ID);
return(groupedTasks
.Select(t => new Tuple <Task, IEnumerable <ProjectComment> >(t.First().Task, t))
.Select(ToFeed));
}
}
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
if (filter.Follow)
{
IEnumerable <string> objects = NotifySource.Instance.GetSubscriptionProvider().GetSubscriptions(NotifyConstants.Event_NewCommentForMessage, NotifySource.Instance.GetRecipientsProvider().GetRecipient(CurrentUserID.ToString()));
if (filter.ProjectIds.Count != 0)
{
objects = objects.Where(r => r.Split('_')[2] == filter.ProjectIds[0].ToString(CultureInfo.InvariantCulture));
}
var ids = objects.Select(r => r.Split('_')[1]).ToArray();
query.Where(Exp.In("t.id", ids));
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
else
{
if (filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("ppp.tenant", "t.tenant_id") & Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false));
query.Where("ppp.participant_id", CurrentUserID);
}
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " pt", Exp.EqColumns("pt.project_id", "t.project_id"));
query.Where("pt.tag_id", filter.TagId);
}
if (filter.UserId != Guid.Empty)
{
query.Where("t.create_by", filter.UserId);
}
if (filter.DepartmentId != Guid.Empty)
{
query.InnerJoin("core_usergroup cug", Exp.EqColumns("cug.tenant", "t.tenant_id") & Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "t.create_by"));
query.Where("cug.groupid", filter.DepartmentId);
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Between("t.create_on", filter.FromDate, filter.ToDate.AddDays(1)));
}
if (filter.MessageStatus.HasValue)
{
query.Where("t.status", filter.MessageStatus.Value);
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
if (FullTextSearch.SupportModule(FullTextSearch.ProjectsMessagesModule))
{
var mIds = FullTextSearch.Search(FullTextSearch.ProjectsMessagesModule.Match(filter.SearchText));
query.Where(Exp.In("t.id", mIds));
}
else
{
query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere));
}
}
if (checkAccess)
{
query.Where(Exp.Eq("p.private", false));
}
else if (!isAdmin)
{
var isInTeam = new SqlQuery(ParticipantTable).Select("security").Where(Exp.EqColumns("p.id", "project_id") & Exp.Eq("removed", false) & Exp.Eq("participant_id", CurrentUserID) & !Exp.Eq("security & " + (int)ProjectTeamSecurity.Messages, (int)ProjectTeamSecurity.Messages));
query.Where(Exp.Eq("p.private", false) | Exp.Eq("p.responsible_id", CurrentUserID) | (Exp.Eq("p.private", true) & Exp.Exists(isInTeam)));
}
return(query);
}
public static Exp GetMailFilterConditions(MailFilter filter, bool skipFolder, string alias)
{
Exp conditions = null;
if (!string.IsNullOrEmpty(alias))
{
alias += ".";
}
if (!skipFolder)
{
conditions = Exp.Eq(alias + MailTable.Columns.folder, filter.PrimaryFolder);
}
if (filter.Unread.HasValue)
{
conditions &= Exp.Eq(alias + MailTable.Columns.unread, filter.Unread);
}
if (filter.Attachments)
{
conditions &= Exp.Gt(alias + MailTable.Columns.attach_count, 0);
}
if (filter.PeriodFrom > 0)
{
var from = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodFrom * 10000);
var to = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodTo * 10000) +
new TimeSpan(1, 0, 0, 0, 0); // 1 day was added to make the "To" date limit inclusive
conditions &= Exp.Between(alias + MailTable.Columns.date_sent, from, to);
}
if (filter.Important)
{
conditions &= Exp.Eq(alias + MailTable.Columns.importance, true);
}
if (!string.IsNullOrEmpty(filter.FindAddress))
{
if (FullTextSearch.SupportModule(FullTextSearch.MailModule))
{
List <int> ids;
if (filter.PrimaryFolder == MailFolder.Ids.sent || filter.PrimaryFolder == MailFolder.Ids.drafts)
{
ids = FullTextSearch.Search(FullTextSearch.MailModule.Match(filter.FindAddress, MailTable.Columns.to));
}
else
{
ids = FullTextSearch.Search(FullTextSearch.MailModule.Match(filter.FindAddress, MailTable.Columns.from));
}
conditions &= Exp.In(alias + MailTable.Columns.id, ids.Take(MailBoxManager.FULLTEXTSEARCH_IDS_COUNT).ToList());
}
else
{
if (filter.PrimaryFolder == MailFolder.Ids.sent || filter.PrimaryFolder == MailFolder.Ids.drafts)
{
conditions &= Exp.Like(alias + MailTable.Columns.to, filter.FindAddress, SqlLike.AnyWhere);
}
else
{
conditions &= Exp.Like(alias + MailTable.Columns.from, filter.FindAddress, SqlLike.AnyWhere);
}
}
}
if (filter.MailboxId.HasValue)
{
conditions &= Exp.Eq(alias + MailTable.Columns.id_mailbox, filter.MailboxId.Value);
}
if (!string.IsNullOrEmpty(filter.SearchFilter))
{
if (FullTextSearch.SupportModule(FullTextSearch.MailModule))
{
var mailModule = FullTextSearch.MailModule.Match(filter.SearchFilter).OrderBy(MailTable.Columns.date_sent, filter.SortOrder == "ascending");
if (filter.PrimaryFolder != 1 && filter.PrimaryFolder != 2)
{
mailModule.AddAttribute("folder", filter.PrimaryFolder);
}
else
{
mailModule.AddAttribute("folder", new[] { 1, 2 });
}
var ids = FullTextSearch.Search(mailModule);
conditions &= Exp.In(alias + MailTable.Columns.id, ids.Take(MailBoxManager.FULLTEXTSEARCH_IDS_COUNT).ToList());
}
else
{
conditions &= Exp.Or(Exp.Like(alias + MailTable.Columns.from, filter.SearchFilter, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.to, filter.SearchFilter, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.cc, filter.SearchFilter,
SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.bcc, filter.SearchFilter,
SqlLike.AnyWhere),
Exp.Like(alias + MailTable.Columns.subject, filter.SearchFilter,
SqlLike.AnyWhere)))));
}
}
return(conditions);
}
public static IEnumerable <AuditEvent> GetByFilter(
Guid?userId = null,
ProductType?productType = null,
ModuleType?moduleType = null,
ActionType?actionType = null,
MessageAction?action = null,
EntryType?entry = null,
string target = null,
DateTime?from = null,
DateTime?to = null,
int startIndex = 0,
int limit = 0)
{
var q = new SqlQuery("audit_events a")
.Select(auditColumns.Select(x => "a." + x).ToArray())
.LeftOuterJoin("core_user u", Exp.EqColumns("a.user_id", "u.id"))
.Select("u.firstname", "u.lastname")
.Where("a.tenant_id", TenantProvider.CurrentTenantID)
.OrderBy("a.date", false);
if (startIndex > 0)
{
q.SetFirstResult(startIndex);
}
if (limit > 0)
{
q.SetMaxResults(limit);
}
if (userId.HasValue && userId.Value != Guid.Empty)
{
q.Where("a.user_id", userId.Value.ToString());
}
var isNeedFindEntry = entry.HasValue && entry.Value != EntryType.None && target != null;
if (action.HasValue && action.Value != MessageAction.None)
{
q.Where("a.action", (int)action);
}
else
{
IEnumerable <KeyValuePair <MessageAction, MessageMaps> > actions = new List <KeyValuePair <MessageAction, MessageMaps> >();
var isFindActionType = actionType.HasValue && actionType.Value != ActionType.None;
if (productType.HasValue && productType.Value != ProductType.None)
{
var productMapper = AuditActionMapper.Mappers.FirstOrDefault(m => m.Product == productType.Value);
if (productMapper != null)
{
if (moduleType.HasValue && moduleType.Value != ModuleType.None)
{
var moduleMapper = productMapper.Mappers.FirstOrDefault(m => m.Module == moduleType.Value);
if (moduleMapper != null)
{
actions = moduleMapper.Actions;
}
}
else
{
actions = productMapper.Mappers.SelectMany(r => r.Actions);
}
}
}
else
{
actions = AuditActionMapper.Mappers
.SelectMany(r => r.Mappers)
.SelectMany(r => r.Actions);
}
if (isFindActionType || isNeedFindEntry)
{
actions = actions
.Where(a => (!isFindActionType || a.Value.ActionType == actionType.Value) && (!isNeedFindEntry || (entry.Value == a.Value.EntryType1) || entry.Value == a.Value.EntryType2))
.ToList();
}
if (isNeedFindEntry)
{
FindByEntry(q, entry.Value, target, actions);
}
else
{
var keys = actions.Select(x => (int)x.Key).ToList();
q.Where(Exp.In("a.action", keys));
}
}
var hasFromFilter = (from.HasValue && from.Value != DateTime.MinValue);
var hasToFilter = (to.HasValue && to.Value != DateTime.MinValue);
if (hasFromFilter || hasToFilter)
{
if (hasFromFilter)
{
if (hasToFilter)
{
q.Where(Exp.Between("a.date", from, to));
}
else
{
q.Where(Exp.Ge("a.date", from));
}
}
else if (hasToFilter)
{
q.Where(Exp.Le("a.date", to));
}
}
using (var db = DbManager.FromHttpContext(dbid))
{
return(db
.ExecuteList(q)
.Select(ToAuditEvent)
.Where(x => x != null));
}
}
public static Exp GetMailFilterConditions(MailFilter filter, bool skipFolder, string alias = "")
{
var conditions = Exp.Empty;
if (!skipFolder)
{
conditions = Exp.Eq(MailTable.Columns.Folder.Prefix(alias), filter.PrimaryFolder);
}
if (filter.Unread.HasValue)
{
conditions &= Exp.Eq(MailTable.Columns.Unread.Prefix(alias), filter.Unread);
}
if (filter.Attachments)
{
conditions &= Exp.Gt(MailTable.Columns.AttachCount.Prefix(alias), 0);
}
if (filter.PeriodFrom > 0)
{
var from = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodFrom * 10000);
var to = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodTo * 10000) +
new TimeSpan(1, 0, 0, 0, 0); // 1 day was added to make the "To" date limit inclusive
conditions &= Exp.Between(MailTable.Columns.DateSent.Prefix(alias), from, to);
}
if (filter.Important)
{
conditions &= Exp.Eq(MailTable.Columns.Importance.Prefix(alias), true);
}
if (filter.WithCalendar)
{
conditions &= !Exp.Eq(MailTable.Columns.CalendarUid.Prefix(alias), null);
}
if (!string.IsNullOrEmpty(filter.FindAddress) && !FullTextSearch.SupportModule(FullTextSearch.MailModule))
{
if (filter.PrimaryFolder == MailFolder.Ids.sent || filter.PrimaryFolder == MailFolder.Ids.drafts)
{
conditions &= Exp.Like(MailTable.Columns.To.Prefix(alias), filter.FindAddress, SqlLike.AnyWhere);
}
else
{
conditions &= Exp.Like(MailTable.Columns.From.Prefix(alias), filter.FindAddress,
SqlLike.AnyWhere);
}
}
if (filter.MailboxId.HasValue)
{
conditions &= Exp.Eq(MailTable.Columns.MailboxId.Prefix(alias), filter.MailboxId.Value);
}
if (!string.IsNullOrEmpty(filter.SearchFilter) && !FullTextSearch.SupportModule(FullTextSearch.MailModule))
{
conditions &=
Exp.Or(Exp.Like(MailTable.Columns.From.Prefix(alias), filter.SearchFilter, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.To.Prefix(alias), filter.SearchFilter, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.Cc.Prefix(alias), filter.SearchFilter,
SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.Bcc.Prefix(alias), filter.SearchFilter,
SqlLike.AnyWhere),
Exp.Like(MailTable.Columns.Subject.Prefix(alias), filter.SearchFilter,
SqlLike.AnyWhere)))));
}
return(conditions);
}
private Exp WhereConditional(String tblAlias,
ICollection <int> exceptIDs,
String searchText,
InvoiceStatus?status,
DateTime issueDateFrom,
DateTime issueDateTo,
DateTime dueDateFrom,
DateTime dueDateTo,
EntityType entityType,
int entityID,
String currency)
{
var tblAliasPrefix = !String.IsNullOrEmpty(tblAlias) ? tblAlias + "." : "";
var conditions = new List <Exp>();
if (entityID > 0)
{
switch (entityType)
{
case EntityType.Contact:
case EntityType.Person:
case EntityType.Company:
conditions.Add(Exp.Eq(tblAliasPrefix + "contact_id", entityID));
break;
case EntityType.Case:
case EntityType.Opportunity:
conditions.Add(Exp.Eq(tblAliasPrefix + "entity_id", entityID) &
Exp.Eq(tblAliasPrefix + "entity_type", (int)entityType));
break;
}
}
if (status != null)
{
conditions.Add(Exp.Eq(tblAliasPrefix + "status", (int)status.Value));
}
if (!String.IsNullOrEmpty(searchText))
{
searchText = searchText.Trim();
var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
.ToArray();
if (keywords.Length > 0)
{
//if (FullTextSearch.SupportModule(FullTextSearch.CRMInvoiceModule))
//{
// ids = FullTextSearch.Search(searchText, FullTextSearch.CRMInvoiceModule)
// .GetIdentifiers()
// .Select(item => Convert.ToInt32(item.Split('_')[1])).Distinct().ToList();
// if (ids.Count == 0) return null;
//}
//else
conditions.Add(BuildLike(new[] { tblAliasPrefix + "number", tblAliasPrefix + "description" }, keywords));
}
}
if (exceptIDs.Count > 0)
{
conditions.Add(!Exp.In(tblAliasPrefix + "id", exceptIDs.ToArray()));
}
if (issueDateFrom != DateTime.MinValue && issueDateTo != DateTime.MinValue)
{
conditions.Add(Exp.Between(tblAliasPrefix + "issue_date", TenantUtil.DateTimeToUtc(issueDateFrom), TenantUtil.DateTimeToUtc(issueDateTo.AddDays(1).AddMinutes(-1))));
}
else if (issueDateFrom != DateTime.MinValue)
{
conditions.Add(Exp.Ge(tblAliasPrefix + "issue_date", TenantUtil.DateTimeToUtc(issueDateFrom)));
}
else if (issueDateTo != DateTime.MinValue)
{
conditions.Add(Exp.Le(tblAliasPrefix + "issue_date", TenantUtil.DateTimeToUtc(issueDateTo.AddDays(1).AddMinutes(-1))));
}
if (dueDateFrom != DateTime.MinValue && dueDateTo != DateTime.MinValue)
{
conditions.Add(Exp.Between(tblAliasPrefix + "due_date", TenantUtil.DateTimeToUtc(dueDateFrom), TenantUtil.DateTimeToUtc(dueDateTo.AddDays(1).AddMinutes(-1))));
}
else if (dueDateFrom != DateTime.MinValue)
{
conditions.Add(Exp.Ge(tblAliasPrefix + "due_date", TenantUtil.DateTimeToUtc(dueDateFrom)));
}
else if (dueDateTo != DateTime.MinValue)
{
conditions.Add(Exp.Le(tblAliasPrefix + "due_date", TenantUtil.DateTimeToUtc(dueDateTo.AddDays(1).AddMinutes(-1))));
}
if (!String.IsNullOrEmpty(currency))
{
conditions.Add(Exp.Eq(tblAliasPrefix + "currency", currency));
}
if (conditions.Count == 0)
{
return(null);
}
return(conditions.Count == 1 ? conditions[0] : conditions.Aggregate((i, j) => i & j));
}
public List <TimeSpend> GetUpdates(DateTime from, DateTime to)
{
return(DbManager
.ExecuteList(CreateQuery()
.Where(Exp.Between("t.date", from, to))).Select(x => ToTimeSpend(x)).ToList());
}
public XDocument BuildSalesByMonthReport(Guid responsibleID, DateTime fromDate, DateTime toDate)
{
var sqlQuery = new SqlQuery("crm_deal tbl_deal")
.Select("MONTH(tbl_deal.actual_close_date) as acdMonth",
"tbl_deal.bid_currency",
@"sum(CASE tbl_deal.bid_type
WHEN 0 THEN
(tbl_deal.bid_value)
ELSE
(tbl_deal.bid_value * tbl_deal.per_period_value)
END) AS bid_value",
"count(tbl_deal.id) as count")
.LeftOuterJoin("crm_deal_milestone tbl_list", Exp.EqColumns("tbl_deal.deal_milestone_id", "tbl_list.id"))
.Where(Exp.Eq("tbl_list.tenant_id", TenantID) & Exp.Eq("tbl_deal.tenant_id", TenantID) &
Exp.Between("tbl_deal.actual_close_date", fromDate, toDate) &
!Exp.Eq("tbl_deal.bid_value", 0) &
Exp.Eq("tbl_list.status", (int)DealMilestoneStatus.ClosedAndWon))
.GroupBy("acdMonth", "tbl_deal.bid_currency");
if (responsibleID != Guid.Empty)
{
sqlQuery.Where(Exp.Eq("tbl_deal.responsible_id", responsibleID));
}
var sqlResult = DbManager.ExecuteList(sqlQuery);
if (sqlResult.Count == 0)
{
return(new XDocument());
}
var result = sqlResult.GroupBy(row =>
{
var bidCurrency = Convert.ToString(row[1]);
if (bidCurrency != Global.TenantSettings.DefaultCurrency.Abbreviation)
{
row[2] = CurrencyProvider.MoneyConvertToDefaultCurrency(Convert.ToDecimal(row[2]), bidCurrency);
}
return(row[0]);
}).Select(group => new[]
{
group.Key,
group.Sum(p => Convert.ToDecimal(p[2])),
group.Sum(p => Convert.ToDecimal(p[3])),
0
}).ToList();
var totalDeals = result.Sum(row => Convert.ToInt32(row[2]));
if (totalDeals == 0)
{
return(new XDocument());
}
foreach (var item in result)
{
item[3] = Convert.ToInt32(item[2]) * 100 / totalDeals;
}
throw new NotImplementedException();
}
public virtual Exp GetExpression()
{
var filterExp = Exp.Eq(MailTable.Columns.Folder.Prefix(MM_ALIAS), (int)Filter.PrimaryFolder);
if (Filter.Unread.HasValue)
{
filterExp &= Exp.Eq(MailTable.Columns.Unread.Prefix(MM_ALIAS), Filter.Unread);
}
if (Filter.Attachments.HasValue)
{
filterExp &= Exp.Gt(MailTable.Columns.AttachCount.Prefix(MM_ALIAS), 0);
}
if (Filter.PeriodFrom.HasValue && Filter.PeriodTo.HasValue)
{
var fromTs = TimeSpan.FromMilliseconds(Filter.PeriodFrom.Value);
var from = Defines.BaseJsDateTime.Add(fromTs);
var toTs = TimeSpan.FromMilliseconds(Filter.PeriodTo.Value);
var to = Defines.BaseJsDateTime.Add(toTs);
filterExp &= Exp.Between(MailTable.Columns.DateSent.Prefix(MM_ALIAS), from, to);
}
if (Filter.Important.HasValue)
{
filterExp &= Exp.Eq(MailTable.Columns.Importance.Prefix(MM_ALIAS), true);
}
if (Filter.WithCalendar.HasValue)
{
filterExp &= !Exp.Eq(MailTable.Columns.CalendarUid.Prefix(MM_ALIAS), null);
}
if (!string.IsNullOrEmpty(Filter.FromAddress) && !FactoryIndexer <MailWrapper> .Support)
{
if (Filter.PrimaryFolder == FolderType.Sent || Filter.PrimaryFolder == FolderType.Draft)
{
filterExp &= Exp.Like(MailTable.Columns.To.Prefix(MM_ALIAS), Filter.FromAddress, SqlLike.AnyWhere);
}
else
{
filterExp &= Exp.Like(MailTable.Columns.From.Prefix(MM_ALIAS), Filter.FromAddress,
SqlLike.AnyWhere);
}
}
if (!string.IsNullOrEmpty(Filter.ToAddress) && !FactoryIndexer <MailWrapper> .Support)
{
if (Filter.PrimaryFolder == FolderType.Sent || Filter.PrimaryFolder == FolderType.Draft)
{
filterExp &= Exp.Like(MailTable.Columns.From.Prefix(MM_ALIAS), Filter.ToAddress, SqlLike.AnyWhere);
}
else
{
filterExp &= Exp.Like(MailTable.Columns.To.Prefix(MM_ALIAS), Filter.ToAddress,
SqlLike.AnyWhere);
}
}
if (Filter.MailboxId.HasValue)
{
filterExp &= Exp.Eq(MailTable.Columns.MailboxId.Prefix(MM_ALIAS), Filter.MailboxId.Value);
}
if (!string.IsNullOrEmpty(Filter.SearchText) && !FactoryIndexer <MailWrapper> .Support)
{
filterExp &=
Exp.Or(Exp.Like(MailTable.Columns.From.Prefix(MM_ALIAS), Filter.SearchText, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.To.Prefix(MM_ALIAS), Filter.SearchText, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.Cc.Prefix(MM_ALIAS), Filter.SearchText,
SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.Bcc.Prefix(MM_ALIAS), Filter.SearchText,
SqlLike.AnyWhere),
Exp.Or(
Exp.Like(MailTable.Columns.Subject.Prefix(MM_ALIAS), Filter.SearchText,
SqlLike.AnyWhere),
Exp.Like(MailTable.Columns.Introduction.Prefix(MM_ALIAS), Filter.SearchText,
SqlLike.AnyWhere)
)
)
)
)
);
}
if (Ids != null && Ids.Any())
{
filterExp &= Exp.In(MailTable.Columns.Id.Prefix(MM_ALIAS), Ids);
}
var exp = Exp.Eq(MailTable.Columns.Tenant.Prefix(MM_ALIAS), Tenant) &
Exp.Eq(MailTable.Columns.User.Prefix(MM_ALIAS), User) &
Exp.Eq(MailTable.Columns.IsRemoved.Prefix(MM_ALIAS), false);
exp &= filterExp;
return(exp);
}
private SqlQuery WhereConditional(
SqlQuery sqlQuery,
Guid responsibleID,
int categoryID,
bool?isClosed,
DateTime fromDate,
DateTime toDate,
EntityType entityType,
int entityID,
int from,
int count,
OrderBy orderBy)
{
if (responsibleID != Guid.Empty)
{
sqlQuery.Where(Exp.Eq("responsible_id", responsibleID));
}
if (entityID > 0)
{
switch (entityType)
{
case EntityType.Contact:
var isCompany = Convert.ToBoolean(DbManager.ExecuteScalar(Query("crm_contact").Select("is_company").Where(Exp.Eq("id", entityID))));
if (isCompany)
{
return(WhereConditional(sqlQuery, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Company, entityID, from, count, orderBy));
}
else
{
return(WhereConditional(sqlQuery, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Person, entityID, from, count, orderBy));
}
case EntityType.Person:
sqlQuery.Where(Exp.Eq("contact_id", entityID));
break;
case EntityType.Company:
var personIDs = GetRelativeToEntity(entityID, EntityType.Person, null).ToList();
if (personIDs.Count == 0)
{
sqlQuery.Where(Exp.Eq("contact_id", entityID));
}
else
{
personIDs.Add(entityID);
sqlQuery.Where(Exp.In("contact_id", personIDs));
}
break;
case EntityType.Case:
case EntityType.Opportunity:
sqlQuery.Where(Exp.Eq("entity_id", entityID) &
Exp.Eq("entity_type", (int)entityType));
break;
}
}
if (isClosed.HasValue)
{
sqlQuery.Where("is_closed", isClosed);
}
if (categoryID > 0)
{
sqlQuery.Where(Exp.Eq("category_id", categoryID));
}
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Between("deadline", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
}
else if (fromDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Ge("deadline", TenantUtil.DateTimeToUtc(fromDate)));
}
else if (toDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Le("deadline", TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
}
if (0 < from && from < int.MaxValue)
{
sqlQuery.SetFirstResult(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery.SetMaxResults(count);
}
sqlQuery.OrderBy("is_closed", true);
if (orderBy != null && Enum.IsDefined(typeof(TaskSortedByType), orderBy.SortedBy))
{
switch ((TaskSortedByType)orderBy.SortedBy)
{
case TaskSortedByType.Title:
sqlQuery
.OrderBy("title", orderBy.IsAsc)
.OrderBy("deadline", true);
break;
case TaskSortedByType.DeadLine:
sqlQuery.OrderBy("deadline", orderBy.IsAsc)
.OrderBy("title", true);
break;
case TaskSortedByType.Category:
sqlQuery.OrderBy("category_id", orderBy.IsAsc)
.OrderBy("deadline", true)
.OrderBy("title", true);
break;
}
}
else
{
sqlQuery.OrderBy("deadline", true)
.OrderBy("title", true);
}
return(sqlQuery);
}
private List <Deal> GetCrudeDeals(
String searchText,
Guid responsibleID,
int milestoneID,
IEnumerable <String> tags,
int contactID,
DealMilestoneStatus?stageType,
bool?contactAlsoIsParticipant,
DateTime fromDate,
DateTime toDate,
int from,
int count,
OrderBy orderBy)
{
var sqlQuery = GetDealSqlQuery(null);
var withParams = !(String.IsNullOrEmpty(searchText) &&
responsibleID == Guid.Empty &&
milestoneID <= 0 &&
(tags == null || !tags.Any()) &&
contactID <= 0 &&
stageType == null &&
contactAlsoIsParticipant == null &&
fromDate == DateTime.MinValue &&
toDate == DateTime.MinValue);
var whereConditional = WhereConditional(new List <int>(),
searchText,
responsibleID,
milestoneID,
tags,
contactID,
stageType,
contactAlsoIsParticipant);
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
{
sqlQuery.Having(Exp.Between("close_date", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate)));
}
else if (withParams && whereConditional == null)
{
return(new List <Deal>());
}
sqlQuery.Where(whereConditional);
if (0 < from && from < int.MaxValue)
{
sqlQuery.SetFirstResult(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery.SetMaxResults(count);
}
if (orderBy != null && Enum.IsDefined(typeof(DealSortedByType), orderBy.SortedBy))
{
switch ((DealSortedByType)orderBy.SortedBy)
{
case DealSortedByType.Title:
sqlQuery.OrderBy("tblDeal.title", orderBy.IsAsc);
break;
case DealSortedByType.BidValue:
sqlQuery.OrderBy("tblDeal.bid_value", orderBy.IsAsc);
break;
case DealSortedByType.Responsible:
sqlQuery.OrderBy("tblDeal.responsible_id", orderBy.IsAsc)
.OrderBy("tblDM.sort_order", orderBy.IsAsc)
.OrderBy("tblDeal.contact_id", true)
.OrderBy("tblDeal.actual_close_date", false)
.OrderBy("tblDeal.expected_close_date", true)
.OrderBy("tblDeal.title", true);
break;
case DealSortedByType.Stage:
sqlQuery.OrderBy("tblDM.sort_order", orderBy.IsAsc)
.OrderBy("tblDeal.contact_id", true)
.OrderBy("tblDeal.actual_close_date", false)
.OrderBy("tblDeal.expected_close_date", true)
.OrderBy("tblDeal.title", true);
break;
case DealSortedByType.DateAndTime:
sqlQuery.OrderBy("close_date", orderBy.IsAsc);
break;
default:
throw new ArgumentException();
}
}
else
{
sqlQuery.OrderBy("tblDM.sort_order", true)
.OrderBy("tblDeal.contact_id", true)
.OrderBy("tblDeal.title", true);
}
using (var db = GetDb())
{
return(db.ExecuteList(sqlQuery).ConvertAll(ToDeal));
}
}
public List <TimeSpend> GetByFilter(TaskFilter filter)
{
var query = CreateQuery();
if (filter.Max != 0 && !filter.Max.Equals(int.MaxValue))
{
query.SetFirstResult((int)filter.Offset);
query.SetMaxResults((int)filter.Max * 2);
}
if (filter.MyProjects || filter.MyMilestones)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("t.project_id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
if (filter.ProjectIds.Count != 0)
{
query.Where(Exp.In("t.project_id", filter.ProjectIds));
}
if (filter.Milestone.HasValue || filter.MyMilestones)
{
query.InnerJoin(MilestonesTable + " pm", Exp.EqColumns("pm.tenant_id", "t.tenant_id") & Exp.EqColumns("pm.project_id", "t.project_id"));
query.InnerJoin(TasksTable + " pt", Exp.EqColumns("pt.tenant_id", "t.tenant_id") & Exp.EqColumns("pt.id", "t.relative_task_id") & Exp.EqColumns("pt.milestone_id", "pm.id"));
if (filter.Milestone.HasValue)
{
query.Where("pm.id", filter.Milestone);
}
else if (filter.MyMilestones)
{
query.Where(Exp.Gt("pm.id", 0));
}
}
if (filter.TagId != 0)
{
query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id"));
query.Where("ptag.tag_id", filter.TagId);
}
if (!string.IsNullOrEmpty(filter.SortBy))
{
var sortColumns = filter.SortColumns["TimeSpend"];
sortColumns.Remove(filter.SortBy);
query.OrderBy("t." + filter.SortBy, filter.SortOrder);
foreach (var sort in sortColumns.Keys)
{
query.OrderBy("t." + sort, sortColumns[sort]);
}
}
if (filter.UserId != Guid.Empty)
{
query.Where("t.person_id", filter.UserId);
}
if (filter.DepartmentId != Guid.Empty)
{
query.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "t.person_id") & Exp.EqColumns("cug.tenant", "t.tenant_id"));
query.Where("cug.groupid", filter.DepartmentId);
}
if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue) &&
!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue))
{
query.Where(Exp.Between("t.date", filter.FromDate, filter.ToDate));
}
if (filter.PaymentStatuses.Any())
{
query.Where(Exp.In("payment_status", filter.PaymentStatuses));
}
if (!string.IsNullOrEmpty(filter.SearchText))
{
query.Where(Exp.Like("t.note", filter.SearchText, SqlLike.AnyWhere));
}
query.GroupBy("t.id");
using (var db = new DbManager(DatabaseId))
{
return(db.ExecuteList(query).ConvertAll(r => ToTimeSpend(r)));
}
}
public static Exp GetMailFilterConditions(MailFilter filter, bool skipFolder, string alias)
{
Exp conditions = null;
if (!string.IsNullOrEmpty(alias))
{
alias += ".";
}
if (!skipFolder)
{
conditions = Exp.Eq(alias + MailTable.Columns.folder, filter.PrimaryFolder);
}
if (filter.CustomLabels != null && filter.CustomLabels.Count > 0)
{
var idsWithAllTagsQuery = new SqlQuery(TagMailTable.name)
.Select(TagMailTable.Columns.id_mail)
.Where(Exp.In(TagMailTable.Columns.id_tag, filter.CustomLabels))
.GroupBy(1)
.Having(Exp.Eq("count(" + TagMailTable.Columns.id_mail + ")", filter.CustomLabels.Count()));
conditions &= Exp.In(alias + MailTable.Columns.id, idsWithAllTagsQuery);
}
if (filter.Unread.HasValue)
{
conditions &= Exp.Eq(alias + MailTable.Columns.unread, filter.Unread);
}
if (filter.Attachments)
{
conditions &= Exp.Gt(alias + MailTable.Columns.attach_count, 0);
}
if (filter.PeriodFrom > 0)
{
var from = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodFrom * 10000);
var to = new DateTime(1970, 1, 1) + new TimeSpan(filter.PeriodTo * 10000) +
new TimeSpan(1, 0, 0, 0, 0); // 1 day was added to make the "To" date limit inclusive
conditions &= Exp.Between(alias + MailTable.Columns.date_sent, from, to);
}
if (filter.Important)
{
conditions &= Exp.Eq(alias + MailTable.Columns.importance, true);
}
if (!string.IsNullOrEmpty(filter.FindAddress))
{
if (filter.PrimaryFolder == MailFolder.Ids.sent || filter.PrimaryFolder == MailFolder.Ids.drafts)
{
conditions &= Exp.Like(alias + MailTable.Columns.to, filter.FindAddress, SqlLike.AnyWhere);
}
else
{
conditions &= Exp.Like(alias + MailTable.Columns.from, filter.FindAddress, SqlLike.AnyWhere);
}
}
if (filter.MailboxId.HasValue)
{
conditions &= Exp.Eq(alias + MailTable.Columns.id_mailbox, filter.MailboxId.Value);
}
if (!string.IsNullOrEmpty(filter.SearchFilter))
{
if (FullTextSearch.SupportModule(FullTextSearch.MailModule))
{
var ids = FullTextSearch.Search(FullTextSearch.MailModule.Match(filter.SearchFilter));
conditions &= Exp.In(alias + MailTable.Columns.id, ids.Take(MailBoxManager.FULLTEXTSEARCH_IDS_COUNT).ToList());
}
else
{
conditions &= Exp.Or(Exp.Like(alias + MailTable.Columns.from, filter.SearchFilter, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.to, filter.SearchFilter, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.cc, filter.SearchFilter,
SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.bcc, filter.SearchFilter,
SqlLike.AnyWhere),
Exp.Like(alias + MailTable.Columns.subject, filter.SearchFilter,
SqlLike.AnyWhere)))));
}
}
return(conditions);
}
public static Exp GetMailFilterConditions(MailFilter filter, bool skip_folder, string alias)
{
Exp conditions = null;
if (!string.IsNullOrEmpty(alias))
{
alias += ".";
}
if (!skip_folder)
{
conditions = Exp.Eq(alias + MailTable.Columns.folder, filter.PrimaryFolder);
}
if (filter.CustomLabels != null && filter.CustomLabels.Count > 0)
{
var ids_with_any_of_tags = new SqlQuery(MailBoxManager.MAIL_TAG_MAIL)
.Select(MailBoxManager.TagMailFields.id_mail)
.Where(Exp.In(MailBoxManager.TagMailFields.id_tag, filter.CustomLabels));
var ids_with_all_tags = new SqlQuery()
.Select(MailBoxManager.TagMailFields.id_mail)
.From(ids_with_any_of_tags, "a")
.GroupBy(MailBoxManager.TagMailFields.id_mail)
.Having(
Exp.Sql("count(a." + MailBoxManager.TagMailFields.id_mail + ")=" + filter.CustomLabels.Count()));
conditions &= Exp.In(alias + MailTable.Columns.id, ids_with_all_tags);
}
if (filter.Unread.HasValue)
{
conditions &= Exp.Eq(alias + MailTable.Columns.unread, filter.Unread);
}
if (filter.Attachments)
{
conditions &= Exp.Gt(alias + MailTable.Columns.attach_count, 0);
}
if (filter.Period_from > 0)
{
var from = new DateTime(1970, 1, 1) + new TimeSpan(filter.Period_from * 10000);
var to = new DateTime(1970, 1, 1) + new TimeSpan(filter.Period_to * 10000) +
new TimeSpan(1, 0, 0, 0, 0); // 1 day was added to make the "To" date limit inclusive
conditions &= Exp.Between(alias + MailTable.Columns.date_sent, from, to);
}
if (filter.Important)
{
conditions &= Exp.Eq(alias + MailTable.Columns.importance, true);
}
if (!string.IsNullOrEmpty(filter.FindAddress))
{
if (filter.PrimaryFolder == MailFolder.Ids.sent || filter.PrimaryFolder == MailFolder.Ids.drafts)
{
conditions &= Exp.Like(alias + MailTable.Columns.to, filter.FindAddress, SqlLike.AnyWhere);
}
else
{
conditions &= Exp.Like(alias + MailTable.Columns.from, filter.FindAddress, SqlLike.AnyWhere);
}
}
if (filter.MailboxId.HasValue)
{
conditions &= Exp.Eq(alias + MailTable.Columns.id_mailbox, filter.MailboxId.Value);
}
if (!string.IsNullOrEmpty(filter.SearchFilter))
{
if (FullTextSearch.SupportModule(FullTextSearch.MailModule))
{
var ids = FullTextSearch.Search(filter.SearchFilter, FullTextSearch.MailModule)
.GetIdentifiers()
.Select(id => int.Parse(id));
// ToDo: replace magic number with ultra cool setting value
conditions &= Exp.In(alias + MailTable.Columns.id, ids.Take(200).ToList());
}
else
{
conditions &= Exp.Or(Exp.Like(alias + MailTable.Columns.from, filter.SearchFilter, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.to, filter.SearchFilter, SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.cc, filter.SearchFilter,
SqlLike.AnyWhere),
Exp.Or(
Exp.Like(alias + MailTable.Columns.bcc, filter.SearchFilter,
SqlLike.AnyWhere),
Exp.Like(alias + MailTable.Columns.subject, filter.SearchFilter,
SqlLike.AnyWhere)))));
}
}
return(conditions);
}
public static IEnumerable <LoginEvent> GetByFilter(
Guid?login = null,
MessageAction?action = null,
DateTime?from = null,
DateTime?to = null,
int startIndex = 0,
int limit = 0)
{
var q = new SqlQuery("login_events l")
.Select(auditColumns.Select(x => "l." + x).ToArray())
.LeftOuterJoin("core_user u", Exp.EqColumns("l.user_id", "u.id"))
.Select("u.firstname", "u.lastname")
.Where("l.tenant_id", TenantProvider.CurrentTenantID)
.OrderBy("l.date", false);
if (startIndex > 0)
{
q.SetFirstResult(startIndex);
}
if (limit > 0)
{
q.SetMaxResults(limit);
}
if (login.HasValue && login.Value != Guid.Empty)
{
q.Where("l.user_id", login.Value.ToString());
}
if (action.HasValue && action.Value != MessageAction.None)
{
q.Where("l.action", (int)action);
}
var hasFromFilter = (from.HasValue && from.Value != DateTime.MinValue);
var hasToFilter = (to.HasValue && to.Value != DateTime.MinValue);
if (hasFromFilter || hasToFilter)
{
if (hasFromFilter)
{
if (hasToFilter)
{
q.Where(Exp.Between("l.date", from, to));
}
else
{
q.Where(Exp.Ge("l.date", from));
}
}
else if (hasToFilter)
{
q.Where(Exp.Le("l.date", to));
}
}
using (var db = GetDbManager())
{
return(db
.ExecuteList(q)
.Select(ToLoginEvent)
.Where(x => x != null));
}
}
private SqlQuery WhereConditional(
SqlQuery sqlQuery,
String alias,
Guid responsibleID,
int categoryID,
bool?isClosed,
DateTime fromDate,
DateTime toDate,
EntityType entityType,
int entityID,
int from,
int count,
OrderBy orderBy)
{
var aliasPrefix = !String.IsNullOrEmpty(alias) ? alias + "." : "";
if (responsibleID != Guid.Empty)
{
sqlQuery.Where(Exp.Eq("responsible_id", responsibleID));
}
if (entityID > 0)
{
switch (entityType)
{
case EntityType.Contact:
var isCompany = true;
isCompany = Db.ExecuteScalar <bool>(Query("crm_contact").Select("is_company").Where(Exp.Eq("id", entityID)));
if (isCompany)
{
return(WhereConditional(sqlQuery, alias, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Company, entityID, from, count, orderBy));
}
else
{
return(WhereConditional(sqlQuery, alias, responsibleID, categoryID, isClosed, fromDate, toDate, EntityType.Person, entityID, from, count, orderBy));
}
case EntityType.Person:
sqlQuery.Where(Exp.Eq(aliasPrefix + "contact_id", entityID));
break;
case EntityType.Company:
var personIDs = GetRelativeToEntity(entityID, EntityType.Person, null).ToList();
if (personIDs.Count == 0)
{
sqlQuery.Where(Exp.Eq(aliasPrefix + "contact_id", entityID));
}
else
{
personIDs.Add(entityID);
sqlQuery.Where(Exp.In(aliasPrefix + "contact_id", personIDs));
}
break;
case EntityType.Case:
case EntityType.Opportunity:
sqlQuery.Where(Exp.Eq(aliasPrefix + "entity_id", entityID) &
Exp.Eq(aliasPrefix + "entity_type", (int)entityType));
break;
}
}
if (isClosed.HasValue)
{
sqlQuery.Where(aliasPrefix + "is_closed", isClosed);
}
if (categoryID > 0)
{
sqlQuery.Where(Exp.Eq(aliasPrefix + "category_id", categoryID));
}
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Between(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate)));
}
else if (fromDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Ge(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(fromDate)));
}
else if (toDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Le(aliasPrefix + "deadline", TenantUtil.DateTimeToUtc(toDate)));
}
if (0 < from && from < int.MaxValue)
{
sqlQuery.SetFirstResult(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery.SetMaxResults(count);
}
sqlQuery.OrderBy(aliasPrefix + "is_closed", true);
if (orderBy != null && Enum.IsDefined(typeof(TaskSortedByType), orderBy.SortedBy))
{
switch ((TaskSortedByType)orderBy.SortedBy)
{
case TaskSortedByType.Title:
sqlQuery
.OrderBy(aliasPrefix + "title", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true);
break;
case TaskSortedByType.DeadLine:
sqlQuery.OrderBy(aliasPrefix + "deadline", orderBy.IsAsc)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.Category:
sqlQuery.OrderBy(aliasPrefix + "category_id", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.ContactManager:
sqlQuery.LeftOuterJoin("core_user u", Exp.EqColumns(aliasPrefix + "responsible_id", "u.id"))
.OrderBy("case when u.lastname is null or u.lastname = '' then 1 else 0 end, u.lastname", orderBy.IsAsc)
.OrderBy("case when u.firstname is null or u.firstname = '' then 1 else 0 end, u.firstname", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
case TaskSortedByType.Contact:
sqlQuery.LeftOuterJoin("crm_contact c_tbl", Exp.EqColumns(aliasPrefix + "contact_id", "c_tbl.id"))
.OrderBy("case when c_tbl.display_name is null then 1 else 0 end, c_tbl.display_name", orderBy.IsAsc)
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
break;
}
}
else
{
sqlQuery
.OrderBy(aliasPrefix + "deadline", true)
.OrderBy(aliasPrefix + "title", true);
}
return(sqlQuery);
}
private Exp WhereConditional(String tblAlias,
ICollection <int> exceptIDs,
String searchText,
InvoiceStatus?status,
DateTime issueDateFrom,
DateTime issueDateTo,
DateTime dueDateFrom,
DateTime dueDateTo,
EntityType entityType,
int entityID,
String currency)
{
var tblAliasPrefix = !String.IsNullOrEmpty(tblAlias) ? tblAlias + "." : "";
var conditions = new List <Exp>();
if (entityID > 0)
{
switch (entityType)
{
case EntityType.Contact:
case EntityType.Person:
case EntityType.Company:
conditions.Add(Exp.Eq(tblAliasPrefix + "contact_id", entityID));
break;
case EntityType.Case:
case EntityType.Opportunity:
conditions.Add(Exp.Eq(tblAliasPrefix + "entity_id", entityID) &
Exp.Eq(tblAliasPrefix + "entity_type", (int)entityType));
break;
}
}
if (status != null)
{
conditions.Add(Exp.Eq(tblAliasPrefix + "status", (int)status.Value));
}
if (!String.IsNullOrEmpty(searchText))
{
searchText = searchText.Trim();
var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
.ToArray();
if (keywords.Length > 0)
{
List <int> invoicesIds;
if (!FactoryIndexer <InvoicesWrapper> .TrySelectIds(s => s.MatchAll(searchText), out invoicesIds))
{
conditions.Add(BuildLike(new[] { tblAliasPrefix + "number", tblAliasPrefix + "description" }, keywords));
}
else
{
conditions.Add(Exp.In(tblAliasPrefix + "id", invoicesIds));
}
}
}
if (exceptIDs.Count > 0)
{
conditions.Add(!Exp.In(tblAliasPrefix + "id", exceptIDs.ToArray()));
}
if (issueDateFrom != DateTime.MinValue && issueDateTo != DateTime.MinValue)
{
conditions.Add(Exp.Between(tblAliasPrefix + "issue_date", TenantUtil.DateTimeToUtc(issueDateFrom), TenantUtil.DateTimeToUtc(issueDateTo.AddDays(1).AddMinutes(-1))));
}
else if (issueDateFrom != DateTime.MinValue)
{
conditions.Add(Exp.Ge(tblAliasPrefix + "issue_date", TenantUtil.DateTimeToUtc(issueDateFrom)));
}
else if (issueDateTo != DateTime.MinValue)
{
conditions.Add(Exp.Le(tblAliasPrefix + "issue_date", TenantUtil.DateTimeToUtc(issueDateTo.AddDays(1).AddMinutes(-1))));
}
if (dueDateFrom != DateTime.MinValue && dueDateTo != DateTime.MinValue)
{
conditions.Add(Exp.Between(tblAliasPrefix + "due_date", TenantUtil.DateTimeToUtc(dueDateFrom), TenantUtil.DateTimeToUtc(dueDateTo.AddDays(1).AddMinutes(-1))));
}
else if (dueDateFrom != DateTime.MinValue)
{
conditions.Add(Exp.Ge(tblAliasPrefix + "due_date", TenantUtil.DateTimeToUtc(dueDateFrom)));
}
else if (dueDateTo != DateTime.MinValue)
{
conditions.Add(Exp.Le(tblAliasPrefix + "due_date", TenantUtil.DateTimeToUtc(dueDateTo.AddDays(1).AddMinutes(-1))));
}
if (!String.IsNullOrEmpty(currency))
{
conditions.Add(Exp.Eq(tblAliasPrefix + "currency", currency));
}
if (conditions.Count == 0)
{
return(null);
}
return(conditions.Count == 1 ? conditions[0] : conditions.Aggregate((i, j) => i & j));
}
public List <RelationshipEvent> GetItems(
String searchText,
EntityType entityType,
int entityID,
Guid createBy,
int categoryID,
DateTime fromDate,
DateTime toDate,
int from,
int count,
OrderBy orderBy)
{
var sqlQuery = GetRelationshipEventQuery(null);
if (entityID > 0)
{
switch (entityType)
{
case EntityType.Contact:
var isCompany = false;
using (var db = GetDb())
{
isCompany = db.ExecuteScalar <bool>(Query("crm_contact").Select("is_company").Where(Exp.Eq("id", entityID)));
}
if (isCompany)
{
return(GetItems(searchText, EntityType.Company, entityID, createBy, categoryID, fromDate, toDate, from, count, orderBy));
}
else
{
return(GetItems(searchText, EntityType.Person, entityID, createBy, categoryID, fromDate, toDate, from, count, orderBy));
}
case EntityType.Person:
sqlQuery.Where(Exp.Eq("contact_id", entityID));
break;
case EntityType.Company:
var personIDs = GetRelativeToEntity(entityID, EntityType.Person, null).ToList();
if (personIDs.Count == 0)
{
sqlQuery.Where(Exp.Eq("contact_id", entityID));
}
else
{
personIDs.Add(entityID);
sqlQuery.Where(Exp.In("contact_id", personIDs));
}
break;
case EntityType.Case:
case EntityType.Opportunity:
sqlQuery.Where(Exp.Eq("entity_id", entityID) &
Exp.Eq("entity_type", (int)entityType));
break;
}
}
if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Between("create_on", TenantUtil.DateTimeToUtc(fromDate), TenantUtil.DateTimeToUtc(toDate.AddDays(1).AddMinutes(-1))));
}
else if (fromDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Ge("create_on", TenantUtil.DateTimeToUtc(fromDate)));
}
else if (toDate != DateTime.MinValue)
{
sqlQuery.Where(Exp.Le("create_on", TenantUtil.DateTimeToUtc(toDate).AddDays(1).AddMinutes(-1)));
}
if (createBy != Guid.Empty)
{
sqlQuery.Where(Exp.Eq("create_by", createBy));
}
if (categoryID != 0)
{
sqlQuery.Where(Exp.Eq("category_id", categoryID));
}
if (!String.IsNullOrEmpty(searchText))
{
searchText = searchText.Trim();
var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
.ToArray();
var ids = new List <int>();
if (!FullTextSearch.SupportModule(FullTextSearch.CRMEventsModule))
{
if (keywords.Length > 0)
{
sqlQuery.Where(BuildLike(new[] { "content" }, keywords));
}
}
else
{
ids = FullTextSearch.Search(searchText, FullTextSearch.CRMEventsModule)
.GetIdentifiers()
.Select(item => Convert.ToInt32(item.Split('_')[1])).Distinct()
.ToList();
if (ids.Count == 0)
{
return(new List <RelationshipEvent>());
}
sqlQuery.Where(Exp.In("id", ids));
}
}
if (0 < from && from < int.MaxValue)
{
sqlQuery.SetFirstResult(from);
}
if (0 < count && count < int.MaxValue)
{
sqlQuery.SetMaxResults(count);
}
if (orderBy != null && Enum.IsDefined(typeof(RelationshipEventByType), orderBy.SortedBy))
{
switch ((RelationshipEventByType)orderBy.SortedBy)
{
case RelationshipEventByType.Category:
sqlQuery.OrderBy("category_id", orderBy.IsAsc);
break;
case RelationshipEventByType.Content:
sqlQuery.OrderBy("content", orderBy.IsAsc);
break;
case RelationshipEventByType.CreateBy:
sqlQuery.OrderBy("create_by", orderBy.IsAsc);
break;
case RelationshipEventByType.Created:
sqlQuery.OrderBy("create_on", orderBy.IsAsc);
break;
}
}
else
{
sqlQuery.OrderBy("create_on", false);
}
using (var db = GetDb())
{
return(db.ExecuteList(sqlQuery)
.ConvertAll(row => ToRelationshipEvent(row)));
}
}
private SqlQuery CreateQueryFilterCount(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess)
{
var minDateTime = DateTime.MinValue;
var maxDateTime = DateTime.MaxValue;
var minDateTimeString = DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss");
query = CreateQueryFilterBase(query, filter);
if (filter.Milestone.HasValue)
{
query.Where("t.milestone_id", filter.Milestone);
}
else if (filter.MyMilestones)
{
if (!filter.MyProjects)
{
query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("t.tenant_id", "ppp.tenant"));
query.Where("ppp.participant_id", CurrentUserID);
}
var existsMilestone = new SqlQuery(MilestonesTable + " m").Select("m.id").Where(Exp.EqColumns("t.milestone_id", "m.id") & Exp.EqColumns("m.tenant_id", "t.tenant_id"));
query.Where(Exp.Exists(existsMilestone));
}
if (filter.ParticipantId.HasValue && filter.ParticipantId.Value.Equals(Guid.Empty))
{
var notExists = new SqlQuery(TasksResponsibleTable + " ptr").Select("ptr.responsible_id").Where(Exp.EqColumns("t.id", "ptr.task_id") & Exp.Eq("ptr.tenant_id", Tenant));
query.Where(!Exp.Exists(notExists));
}
var hasFromDate = !filter.FromDate.Equals(minDateTime) && !filter.FromDate.Equals(maxDateTime);
var hasToDate = !filter.ToDate.Equals(minDateTime) && !filter.ToDate.Equals(maxDateTime);
if (hasFromDate && hasToDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m").Select("m.id").Where(Exp.EqColumns("m.id", "t.milestone_id") & Exp.EqColumns("m.tenant_id", "t.tenant_id") & Exp.Between("m.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate), TenantUtil.DateTimeFromUtc(filter.ToDate)));
var expExists = Exp.Exists(existsMilestone) & Exp.Eq("t.deadline", minDateTimeString);
query.Where(Exp.Between("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate), TenantUtil.DateTimeFromUtc(filter.ToDate)) | expExists);
}
else if (hasFromDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m")
.Select("m.id")
.Where(Exp.EqColumns("m.id", "t.milestone_id"))
.Where(Exp.EqColumns("m.tenant_id", "t.tenant_id"))
.Where(Exp.Ge("m.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate)))
.Where(Exp.Eq("t.deadline", minDateTimeString));
query.Where(Exp.Ge("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate))
| Exp.Exists(existsMilestone));
}
else if (hasToDate)
{
var existsMilestone = new SqlQuery(MilestonesTable + " m")
.Select("m.id")
.Where(Exp.EqColumns("m.id", "t.milestone_id"))
.Where(Exp.EqColumns("m.tenant_id", "t.tenant_id"))
.Where(Exp.Le("m.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate)))
.Where(!Exp.Eq("m.deadline", minDateTimeString))
.Where("t.deadline", minDateTimeString);
query.Where(!Exp.Eq("t.deadline", minDateTimeString)
& Exp.Le("t.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate))
| Exp.Exists(existsMilestone));
}
CheckSecurity(query, filter, isAdmin, checkAccess);
return(query);
}
public List <Milestone> GetUpdates(DateTime from, DateTime to)
{
return(DbManager.ExecuteList(CreateQuery()
.Select("t.status_changed")
.Where(Exp.Between("t.create_on", from, to) | Exp.Between("t.last_modified_on", from, to) | Exp.Between("t.deadline", from, to)))
.ConvertAll(x =>
{
var ms = ToMilestone(x);
ms.StatusChangedOn = Convert.ToDateTime(x.Last());
return ms;
}).ToList());
}