public static bool HaveUserActivity(int tenantID, Guid productID, int action) { var q = new SqlQuery("webstudio_useractivity").Where("tenantid", tenantID); if (productID != default(Guid)) { q.Where("productid", productID.ToString()); } if (action != UserActivityConstants.AllActionType) { q.Where("actiontype", action); } using (var db = GetDbManager()) { return db.ExecuteScalar<int>(q.SelectCount()) > 0; } }
public bool IsInstallRegistered(string userEmail, MobileAppType? appType) { var query = new SqlQuery("mobile_app_install") .SelectCount() .Where("user_email", userEmail); if (appType.HasValue) query.Where("app_type", (int) appType.Value); using (var db = GetDbManager()) { return db.ExecuteScalar<int>(query) > 0; } }
//NOTE: in tuple return first = total count, second = user readed count public CommentCount GetCountWithReaded(string key, Guid userId) { using (DbManager dbMan = GetManager()) { SqlQuery query = new SqlQuery(CommentTable).Select("count(id)", "count(user_id)") .LeftOuterJoin(CommentReadedTable, Exp.EqColumns("id", "comment_id")) .Where("comment_key", key); if (userId != Guid.Empty) query = query.Where(Exp.Eq("user_id", userId.ToString()) | Exp.Eq("user_id", null)); return dbMan.ExecuteList(query).Select(x => new CommentCount(Convert.ToInt64(x[0]), Convert.ToInt64(x[1]))) .SingleOrDefault(); } }
public static bool GetIsMobileAppUser(Guid userId, DeviceType? type) { string userEmail = CoreContext.UserManager.GetUsers(userId).Email; var query = new SqlQuery("push_app_user") .SelectCount() .Where("user_email", userEmail); if (type.HasValue) query.Where("device_type", (int)type.Value); using (var db = new DbManager("core")) { return db.ExecuteScalar<int>(query) > 0; } }
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin, bool checkAccess) { if (filter.TagId != 0) { query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "p.id")); query.Where("ptag.tag_id", filter.TagId); } if (filter.HasUserId || (filter.ParticipantId.HasValue && filter.ParticipantId != Guid.Empty)) { var existParticipant = new SqlQuery(ParticipantTable + " ppp").Select("ppp.participant_id").Where(Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.Eq("ppp.tenant", Tenant)); if (filter.DepartmentId != Guid.Empty) { existParticipant.InnerJoin("core_usergroup cug", Exp.Eq("cug.removed", false) & Exp.EqColumns("cug.userid", "ppp.participant_id") & Exp.EqColumns("cug.tenant", "ppp.tenant")); existParticipant.Where("cug.groupid", filter.DepartmentId); } if (filter.ParticipantId.HasValue && filter.ParticipantId != Guid.Empty) { existParticipant.Where(Exp.Eq("ppp.participant_id", filter.ParticipantId.ToString())); } query.Where(Exp.Exists(existParticipant)); } if (filter.UserId != Guid.Empty) { query.Where("responsible_id", filter.UserId); } if (filter.Follow) { query.InnerJoin(FollowingProjectTable + " pfpp", Exp.EqColumns("p.id", "pfpp.project_id")); query.Where(Exp.Eq("pfpp.participant_id", CurrentUserID)); } if (filter.ProjectStatuses.Count != 0) { query.Where(Exp.Eq("p.status", filter.ProjectStatuses.First())); } if (!string.IsNullOrEmpty(filter.SearchText)) { if (FullTextSearch.SupportModule(FullTextSearch.ProjectsModule)) { var projIds = FullTextSearch.Search(FullTextSearch.ProjectsModule.Match(filter.SearchText)); query.Where(Exp.In("p.id", projIds)); } else { query.Where(Exp.Like("p.title", filter.SearchText, SqlLike.AnyWhere)); } } query.GroupBy("p.id"); 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)); 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 List<ResWord> SearchAll(string projectName, string moduleName, string languageTo, string searchText, string searchType) { using (var dbManager = new DbManager("tmresource")) { var sql = new SqlQuery("res_data") .Select("title", "fileid", "textValue", "resName", "moduleName", "projectName") .InnerJoin("res_files", Exp.EqColumns("res_files.ID", "res_data.fileID")) .Where("cultureTitle", languageTo) .Where("flag != 4") .Where(Exp.Like(searchType, searchText)) .Where("resourceType", "text") .OrderBy("textValue", true); if (!string.IsNullOrEmpty(projectName) && projectName != "All") { sql.Where("projectName", projectName); if (!string.IsNullOrEmpty(moduleName) && moduleName != "All") sql.Where("moduleName", moduleName); } return dbManager.ExecuteList(sql).ConvertAll(r => GetSearchWord(r)); } }
public static IEnumerable<ResWord> GetListResWords(ResCurrent current, string search) { using (var dbManager = new DbManager("tmresource")) { var exist = new SqlQuery("res_data rd3") .Select("rd3.title") .Where("rd3.fileid = rd1.fileid") .Where("rd3.title = concat('del_', rd1.title)") .Where("rd3.cultureTitle = rd1.cultureTitle"); var sql = new SqlQuery("res_data rd1") .Select("rd1.title", "rd1.fileid", "rd1.textValue", "rd1.description", "rd1.flag", "rd1.link", "rf.resName", "rd2.id", "rd2.flag", "rd2.textValue") .LeftOuterJoin("res_data rd2", Exp.EqColumns("rd1.fileid", "rd2.fileid") & Exp.EqColumns("rd1.title", "rd2.title") & Exp.Eq("rd2.cultureTitle", current.Language.Title)) .InnerJoin("res_files rf", Exp.EqColumns("rf.ID", "rd1.fileID")) .Where("rf.moduleName", current.Module.Name) .Where("rf.projectName", current.Project.Name) .Where("rd1.cultureTitle", "Neutral") .Where("rd1.flag != 4") .Where("rd1.resourceType", "text") .Where(!Exp.Like("rd1.title", @"del\_", SqlLike.StartWith) & !Exp.Exists(exist)) .OrderBy("rd1.id", true); if (!String.IsNullOrEmpty(search)) sql.Where(Exp.Like("rd1.textvalue", search)); return dbManager.ExecuteList(sql).ConvertAll(r => { var word = GetWord(r); word.ResFile.FileName = Convert.ToString(r[6]); if (r[7] != null) { word.Status = (int)r[8] == 3 ? WordStatusEnum.Changed : WordStatusEnum.Translated; word.ValueTo = Convert.ToString(r[9]); } else { word.Status = WordStatusEnum.Untranslated; } return word; }).OrderBy(r => r.ValueFrom); } }
private List<MailMessageItem> GetFilteredChains( IDbManager db, int id_tenant, string id_user, MailFilter filter, DateTime? utc_chain_from_date, int from_message, bool? prev_flag, out bool has_more) { var res = new List<MailMessageItem>(); var chains_to_skip = new List<ChainInfo>(); var skip_flag = false; var chunck_index = 0; var sort_order = filter.SortOrder == "ascending"; if (prev_flag.GetValueOrDefault(false)) sort_order = !sort_order; var query_messages = new SqlQuery(MailTable.name) .Select( MailTable.Columns.id, MailTable.Columns.from, MailTable.Columns.to, MailTable.Columns.reply, MailTable.Columns.subject, MailTable.Columns.importance, MailTable.Columns.date_sent, MailTable.Columns.size, MailTable.Columns.attach_count, MailTable.Columns.unread, MailTable.Columns.is_answered, MailTable.Columns.is_forwarded, MailTable.Columns.is_from_crm, MailTable.Columns.is_from_tl, MailTable.Columns.folder_restore, MailTable.Columns.folder, MailTable.Columns.chain_id, MailTable.Columns.id_mailbox, MailTable.Columns.chain_date) .Where(GetUserWhere(id_user, id_tenant)) .Where(MailTable.Columns.is_removed, false) .ApplyFilter(filter) .OrderBy(MailTable.Columns.chain_date, sort_order); if (null != utc_chain_from_date) { query_messages = query_messages.Where(sort_order ? Exp.Ge(MailTable.Columns.chain_date, utc_chain_from_date) : Exp.Le(MailTable.Columns.chain_date, utc_chain_from_date)); skip_flag = true; } // We are increasing the size of the page to check whether it is necessary to show the Next button. while (res.Count < filter.PageSize + 1) { query_messages.SetFirstResult(chunck_index * chunk_size * filter.PageSize).SetMaxResults(chunk_size * filter.PageSize); chunck_index++; var tenant_obj = CoreContext.TenantManager.GetTenant(id_tenant); var list = db .ExecuteList(query_messages) .ConvertAll(r => ConvertToConversation(r, tenant_obj)); if (0 == list.Count) break; foreach (var item in list) { var chain_info = new ChainInfo {id = item.ChainId, mailbox = item.MailboxId}; // Skip chains that was stored before and within from_message's chain. if (skip_flag) { var tenant = CoreContext.TenantManager.GetTenant(id_tenant); if (item.ChainDate != TenantUtil.DateTimeFromUtc(tenant, utc_chain_from_date.GetValueOrDefault())) skip_flag = false; else { if (item.Id == from_message) skip_flag = false; chains_to_skip.Add(chain_info); continue; } } if (chains_to_skip.Contains(chain_info)) continue; var already_contains = false; foreach(var chain in res){ if(chain.ChainId == item.ChainId && chain.MailboxId == item.MailboxId){ already_contains = true; if(chain.Date < item.Date) res[res.IndexOf(chain)] = item; break; } } if(!already_contains) res.Add(item); if (filter.PageSize + 1 == res.Count) break; } var is_all_needed_conversation_gathered = filter.PageSize + 1 == res.Count; if (is_all_needed_conversation_gathered) break; var is_enough_messages_for_page = chunk_size*filter.PageSize <= list.Count; if (!is_enough_messages_for_page) break; } has_more = res.Count > filter.PageSize; if (has_more) res.RemoveAt(filter.PageSize); return res; }
public IEnumerable<SubscriptionMethod> GetSubscriptionMethods(int tenant, string sourceId, string actionId, string recipientId) { if (sourceId == null) throw new ArgumentNullException("sourceId"); if (actionId == null) throw new ArgumentNullException("actionId"); var q = new SqlQuery("core_subscriptionmethod") .Select("tenant", "recipient", "sender") .Where(Exp.Eq("tenant", -1) | Exp.Eq("tenant", tenant)) .Where("source", sourceId) .Where("action", actionId) .OrderBy("tenant", true); if (recipientId != null) q.Where("recipient", recipientId); var methods = ExecList(q) .ConvertAll(r => { return new SubscriptionMethod { Tenant = Convert.ToInt32(r[0]), SourceId = sourceId, ActionId = actionId, RecipientId = (string)r[1], Methods = Convert.ToString(r[2]).Split(new[] { '|' }, StringSplitOptions.RemoveEmptyEntries), }; }); var result = methods.ToList(); var common = new Dictionary<string, SubscriptionMethod>(); foreach (var m in methods) { var key = m.SourceId + m.ActionId + m.RecipientId; if (m.Tenant == Tenant.DEFAULT_TENANT) { m.Tenant = tenant; common.Add(key, m); } else { SubscriptionMethod r; if (common.TryGetValue(key, out r)) { result.Remove(r); } } } return result; }
public IEnumerable<Tag> GetNewTags(Guid subject, Folder parentFolder, bool deepSearch) { using (var DbManager = GetDbManager()) { if (parentFolder == null || parentFolder.ID == null) throw new ArgumentException("folderId"); var result = new List<Tag>(); var monitorFolderIds = new[] {parentFolder.ID}.AsEnumerable(); var getBaseSqlQuery = new Func<SqlQuery>(() => { var fnResult = Query("files_tag ft") .Select("ft.name", "ft.flag", "ft.owner", "ftl.entry_id", "ftl.entry_type", "ftl.tag_count", "ft.id") .Distinct() .InnerJoin("files_tag_link ftl", Exp.EqColumns("ft.tenant_id", "ftl.tenant_id") & Exp.EqColumns("ft.id", "ftl.tag_id")) .Where(Exp.Eq("ft.flag", (int) TagType.New)); if (subject != Guid.Empty) fnResult.Where(Exp.Eq("ft.owner", subject)); return fnResult; }); var tempTags = Enumerable.Empty<Tag>(); if (parentFolder.FolderType == FolderType.SHARE) { var shareQuery = new Func<SqlQuery>(() => getBaseSqlQuery().InnerJoin("files_security fs", Exp.EqColumns("fs.tenant_id", "ftl.tenant_id") & Exp.EqColumns("fs.entry_id", "ftl.entry_id") & Exp.EqColumns("fs.entry_type", "ftl.entry_type"))); var tmpShareFileTags = DbManager.ExecuteList( shareQuery().InnerJoin("files_file f", !Exp.Eq("f.create_by", subject) & Exp.EqColumns("f.id", "ftl.entry_id") & Exp.Eq("ftl.entry_type", FileEntryType.File)) .Select(GetRootFolderType("folder_id"))) .Where(r => ParseRootFolderType(r[7]) == FolderType.USER).ToList() .ConvertAll(r => ToTag(r)); tempTags = tempTags.Concat(tmpShareFileTags); var tmpShareFolderTags = DbManager.ExecuteList( shareQuery().InnerJoin("files_folder f", !Exp.Eq("f.create_by", subject) & Exp.EqColumns("f.id", "ftl.entry_id") & Exp.Eq("ftl.entry_type", FileEntryType.Folder)) .Select(GetRootFolderType("parent_id"))) .Where(r => ParseRootFolderType(r[7]) == FolderType.USER).ToList() .ConvertAll(r => ToTag(r)); tempTags = tempTags.Concat(tmpShareFolderTags); var tmpShareSboxTags = DbManager.ExecuteList( shareQuery() .InnerJoin("files_thirdparty_id_mapping m", Exp.EqColumns("m.hash_id", "ftl.entry_id")) .InnerJoin("files_thirdparty_account ac", Exp.Sql("m.id Like concat('sbox-', ac.id, '%')") & !Exp.Eq("ac.user_id", subject) & Exp.Eq("ac.folder_type", FolderType.USER) ) ).ConvertAll(r => ToTag(r)); tempTags = tempTags.Concat(tmpShareSboxTags); } else if (parentFolder.FolderType == FolderType.Projects) tempTags = DbManager.ExecuteList(getBaseSqlQuery().InnerJoin("files_bunch_objects fbo", Exp.EqColumns("fbo.tenant_id", "ftl.tenant_id") & Exp.EqColumns("fbo.left_node", "ftl.entry_id") & Exp.Eq("ftl.entry_type", (int) FileEntryType.Folder)) .Where(Exp.Eq("fbo.tenant_id", TenantID) & Exp.Like("fbo.right_node", "projects/project/", SqlLike.StartWith))) .ConvertAll(r => ToTag(r)); if (tempTags.Any()) { if (!deepSearch) return tempTags; monitorFolderIds = monitorFolderIds.Concat(tempTags.Where(x => x.EntryType == FileEntryType.Folder).Select(x => x.EntryId)); result.AddRange(tempTags); } var subFoldersSqlQuery = new SqlQuery("files_folder_tree") .Select("folder_id") .Where(Exp.In("parent_id", monitorFolderIds.ToArray())); if (!deepSearch) subFoldersSqlQuery.Where(Exp.Eq("level", 1)); monitorFolderIds = monitorFolderIds.Concat(DbManager.ExecuteList(subFoldersSqlQuery).ConvertAll(x => x[0])); var newTagsForFolders = DbManager.ExecuteList(getBaseSqlQuery() .Where(Exp.In("ftl.entry_id", monitorFolderIds.ToArray()) & Exp.Eq("ftl.entry_type", (int) FileEntryType.Folder))) .ConvertAll(r => ToTag(r)); result.AddRange(newTagsForFolders); var newTagsForFiles = DbManager.ExecuteList(getBaseSqlQuery() .InnerJoin("files_file ff", Exp.EqColumns("ftl.tenant_id", "ff.tenant_id") & Exp.EqColumns("ftl.entry_id", "ff.id")) .Where(Exp.In("ff.folder_id", (deepSearch ? monitorFolderIds.ToArray() : new[] {parentFolder.ID})) & Exp.Eq("ftl.entry_type", (int) FileEntryType.File))) .ConvertAll(r => ToTag(r)); result.AddRange(newTagsForFiles); if (parentFolder.FolderType == FolderType.USER || parentFolder.FolderType == FolderType.COMMON) { var folderType = parentFolder.FolderType; var querySelect = new SqlQuery("files_thirdparty_account") .Select("id") .Where("tenant_id", TenantID) .Where("folder_type", (int) folderType); if (folderType == FolderType.USER) querySelect = querySelect.Where(Exp.Eq("user_id", subject.ToString())); var sboxFolderIds = DbManager.ExecuteList(querySelect).ConvertAll(r => "sbox-" + r[0]); var newTagsForSBox = DbManager.ExecuteList(getBaseSqlQuery() .InnerJoin("files_thirdparty_id_mapping mp", Exp.EqColumns("ftl.entry_id", "mp.hash_id")) .Where(Exp.In("mp.id", sboxFolderIds) & Exp.Eq("ft.owner", subject) & Exp.Eq("ftl.entry_type", (int) FileEntryType.Folder))) .ConvertAll(r => ToTag(r)); result.AddRange(newTagsForSBox); } return result; } }
public IList<object[]> BuildProjectWithoutOpenMilestone(ReportFilter filter) { IntersectUsersAndProjects(filter); IntersectTagsAndProjects(filter); var query = new SqlQuery("projects_projects p") .LeftOuterJoin("projects_milestones m", Exp.EqColumns("p.id", "m.project_id") & Exp.EqColumns("p.tenant_id", "m.tenant_id")) .Select("p.id") .Where("p.tenant_id", Tenant) .GroupBy(1) .Having(Exp.Eq("sum(case m.status when 0 then 1 else 0 end)", 0)); if (filter.HasProjectIds) { query.Where(Exp.In("p.id", filter.ProjectIds)); } var ids = DbManager .ExecuteList(query) .ConvertAll(r => Convert.ToInt32(r[0])) .ToArray(); filter.SetProjectIds(ids); return BuildProjectListReport(filter); }
public IList<object[]> BuildProjectListReport(ReportFilter filter) { IntersectUsersAndProjects(filter); IntersectTagsAndProjects(filter); var query = new SqlQuery("projects_projects p") .Select("p.id") .Select("p.title") .Select("p.responsible_id") .Select("p.status") .Select(new SqlQuery("projects_milestones m").SelectCount().Where(Exp.EqColumns("m.project_id", "p.id"))) .Select(new SqlQuery("projects_tasks t").SelectCount().Where(Exp.EqColumns("t.project_id", "p.id"))) .Select(new SqlQuery("projects_project_participant pp").SelectCount().Where(Exp.EqColumns("pp.project_id", "p.id") & Exp.Eq("removed", false))) .Select("p.private") .Where("p.tenant_id", Tenant) .OrderBy("p.title", true); if (filter.HasProjectStatuses) { query.Where(Exp.In("p.status", filter.ProjectStatuses)); } if (filter.HasProjectIds) { query.Where(Exp.In("p.id", filter.ProjectIds)); } return DbManager .ExecuteList(query) .ConvertAll(r => new object[] { Convert.ToInt32(r[0]), (string)r[1], ToGuid(r[2]), ToString((ProjectStatus)Convert.ToInt32(r[3])), (long)r[4], (long)r[5], (long)r[6], Convert.ToBoolean(r[7]), }); }
public IList<object[]> BuildTimeReport(ReportFilter filter) { var query = new SqlQuery("projects_time_tracking t") .LeftOuterJoin("projects_tasks r", Exp.EqColumns("t.relative_task_id", "r.id") & Exp.EqColumns("r.tenant_id", "t.tenant_id")) .Select("t.person_id", "t.project_id", "t.relative_task_id", "r.title") .SelectSum("t.hours") .Where("t.tenant_id", Tenant) .Where(!Exp.Eq("t.relative_task_id", 0)) .Where(Exp.Between("t.date", filter.GetFromDate(true), filter.GetToDate(true))) .GroupBy(1, 2, 3); if (filter.HasProjectIds) { query.Where(Exp.In("t.project_id", filter.ProjectIds)); } if (filter.HasUserId) { query.Where(Exp.In("t.person_id", filter.GetUserIds())); } return DbManager.ExecuteList(query); }
public IList<object[]> BuildTaskListReport(ReportFilter filter) { IntersectTagsAndProjects(filter); var query = new SqlQuery("projects_tasks t") .InnerJoin("projects_projects p", Exp.EqColumns("p.tenant_id", "t.tenant_id") & Exp.EqColumns("t.project_id", "p.id")) .LeftOuterJoin("projects_milestones m", Exp.EqColumns("m.tenant_id", "t.tenant_id") & Exp.EqColumns("t.milestone_id", "m.id")) .LeftOuterJoin("projects_tasks_responsible ptr", Exp.EqColumns("ptr.tenant_id", "t.tenant_id") & Exp.EqColumns("ptr.task_id", "t.id")) .Select("p.id", "p.title") .Select("m.id", "m.title", "m.deadline", "m.status") .Select("t.id", "t.title", "COALESCE(ptr.responsible_id, t.responsible_id)", "t.status", "t.deadline", "substring(t.description, 0, 510)") .Where("t.tenant_id", Tenant) .OrderBy("p.title", true) .OrderBy("p.id", true) .OrderBy("m.status", true) .OrderBy("m.deadline", true) .OrderBy("m.title", true) .OrderBy("m.id", true) .OrderBy("t.sort_order", false) .OrderBy("t.status", true) .OrderBy("t.priority", true) .OrderBy("t.create_on", true); if (filter.HasProjectIds) { query.Where(Exp.In("p.id", filter.ProjectIds)); } if (filter.HasTaskStatuses) { query.Where(Exp.In("t.status", filter.TaskStatuses)); } if (filter.HasUserId) { query.Where(Exp.In("ptr.responsible_id", filter.GetUserIds())); } if (filter.GetToDate() != DateTime.MaxValue) { query.Where(Exp.Between("t.deadline", new DateTime(1900, 1, 1), filter.GetToDate())); } if(!filter.NoResponsible) { query.Where(!Exp.Eq("ptr.responsible_id", null) & !Exp.Eq("t.responsible_id", Guid.Empty)); } query.GroupBy("t.id"); return DbManager .ExecuteList(query) .ConvertAll(r => new object[] { Convert.ToInt32(r[0]), (string)r[1], r[2] != null ? Convert.ToInt32(r[2]) : 0, (string)r[3], r[4] != null ? (TenantUtil.DateTimeFromUtc((DateTime)r[4])).ToString("d") : null, r[5] != null ? Convert.ToInt32(r[5]) : -1, Convert.ToInt32(r[6]), (string)r[7], ToGuid(r[8]), (TaskStatus)Convert.ToInt32(r[9]), r[10] != null && !DateTime.MinValue.Equals(r[10]) ? (TenantUtil.DateTimeFromUtc((DateTime)r[10])).ToString("d") : null, ASC.Common.Utils.HtmlUtil.GetText((string)r[11], 500) }); }
public IList<object[]> BuildUsersStatisticsReport(ReportFilter filter) { var query = new SqlQuery("projects_tasks t") .Select("r.responsible_id") .InnerJoin("projects_tasks_responsible r", Exp.EqColumns("r.task_id", "t.id") & Exp.EqColumns("r.tenant_id", "t.tenant_id")) .SelectSum("0") .SelectSum("case t.status when 2 then 0 else 1 end") .SelectSum("case t.status when 2 then 1 else 0 end") .Where("t.tenant_id", Tenant) .GroupBy(1); if (filter.HasUserId) { query.Where(Exp.In("r.responsible_id", filter.GetUserIds())); } else { query.Where(!Exp.Eq("r.responsible_id", Guid.Empty.ToString())); } if (filter.HasProjectIds) { query.Where(Exp.In("t.project_id", filter.ProjectIds)); } return DbManager .ExecuteList(query) .ConvertAll(r => new object[] { ToGuid(r[0]), Convert.ToInt64(r[1]), Convert.ToInt64(r[2]), Convert.ToInt64(r[3]) }); }
private static SqlQuery GetUserGroupRefQuery(int tenant, DateTime from) { var q = new SqlQuery("core_usergroup").Select("userid", "groupid", "ref_type", "removed", "last_modified", "tenant"); if (tenant != Tenant.DEFAULT_TENANT) q.Where("tenant", tenant); if (from != default(DateTime)) q.Where(Exp.Ge("last_modified", from)); return q; }
private SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin) { if (filter.MilestoneStatuses.Count != 0) { query.Where("t.status", filter.MilestoneStatuses.First()); } if (filter.ProjectIds.Count != 0) { query.Where(Exp.In("t.project_id", filter.ProjectIds)); } else { if (filter.MyProjects) { query.InnerJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.EqColumns("ppp.tenant", "t.tenant_id")); query.Where("ppp.participant_id", CurrentUserID); } } if (filter.UserId != Guid.Empty) { query.Where(Exp.Eq("t.responsible_id", filter.UserId)); } if (filter.TagId != 0) { query.InnerJoin(ProjectTagTable + " ptag", Exp.EqColumns("ptag.project_id", "t.project_id")); query.Where("ptag.tag_id", filter.TagId); } if (filter.ParticipantId.HasValue) { var existSubtask = new SqlQuery(SubtasksTable + " pst").Select("pst.task_id").Where(Exp.EqColumns("t.tenant_id", "pst.tenant_id") & Exp.EqColumns("pt.id", "pst.task_id") & Exp.Eq("pst.status", TaskStatus.Open)); var existResponsible = new SqlQuery(TasksResponsibleTable + " ptr1").Select("ptr1.task_id").Where(Exp.EqColumns("t.tenant_id", "ptr1.tenant_id") & Exp.EqColumns("pt.id", "ptr1.task_id")); existSubtask.Where(Exp.Eq("pst.responsible_id", filter.ParticipantId.ToString())); existResponsible.Where(Exp.Eq("ptr1.responsible_id", filter.ParticipantId.ToString())); query.LeftOuterJoin(TasksTable + " as pt", Exp.EqColumns("pt.milestone_id", "t.id") & Exp.EqColumns("pt.tenant_id", "t.tenant_id")); query.Where(Exp.Exists(existSubtask) | Exp.Exists(existResponsible)); } if (!filter.FromDate.Equals(DateTime.MinValue) && !filter.FromDate.Equals(DateTime.MaxValue)) { query.Where(Exp.Ge("t.deadline", TenantUtil.DateTimeFromUtc(filter.FromDate))); } if (!filter.ToDate.Equals(DateTime.MinValue) && !filter.ToDate.Equals(DateTime.MaxValue)) { query.Where(Exp.Le("t.deadline", TenantUtil.DateTimeFromUtc(filter.ToDate))); } if (!string.IsNullOrEmpty(filter.SearchText)) { query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere)); } if (!isAdmin) { 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.Sql("ppp.security IS NOT NULL") & Exp.Eq("ppp.removed", false); var canReadMilestones = !Exp.Eq("security & " + (int)ProjectTeamSecurity.Milestone, (int)ProjectTeamSecurity.Milestone); var responsible = Exp.Eq("t.responsible_id", CurrentUserID); query.Where(Exp.Eq("p.private", false) | isInTeam & (responsible | canReadMilestones)); } return query; }
private void IntersectUsersAndProjects(ReportFilter filter) { if (filter.HasUserId) { var query = new SqlQuery("projects_project_participant") .Select("project_id") .Where(Exp.In("participant_id", filter.GetUserIds()) & Exp.Eq("removed", false)) .GroupBy(1); if (filter.HasProjectIds) { query.Where(Exp.In("project_id", filter.ProjectIds)); } var ids = DbManager .ExecuteList(query) .ConvertAll(r => Convert.ToInt32(r[0])) .ToArray(); filter.SetProjectIds(ids); filter.UserId = Guid.Empty; filter.DepartmentId = Guid.Empty; } }
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; }
public IList<object[]> BuildMilestonesReport(ReportFilter filter) { IntersectTagsAndProjects(filter); var query = new SqlQuery() .From("projects_projects p") .InnerJoin("projects_milestones m", Exp.EqColumns("p.id", "m.project_id") & Exp.EqColumns("m.tenant_id", "p.tenant_id")) .Select("p.id", "p.title", "m.id", "m.title", "m.deadline", "m.status") .Where("m.status", MilestoneStatus.Open) .Where("p.tenant_id", Tenant) .OrderBy("p.title", true) .OrderBy("m.deadline", false); if (filter.HasProjectIds) { query.Where(Exp.In("p.id", filter.ProjectIds)); } if (filter.GetFromDate() != DateTime.MinValue) { query.Where(Exp.Ge("m.deadline", filter.GetFromDate())); } if (filter.GetToDate() != DateTime.MaxValue) { query.Where(Exp.Le("m.deadline", filter.GetToDate())); } return DbManager .ExecuteList(query) .ConvertAll(r => new object[] { Convert.ToInt32(r[0]), (string)r[1], Convert.ToInt32(r[2]), (string)r[3], ((DateTime)r[4]).ToString("d"), ToString((MilestoneStatus)Convert.ToInt32(r[5])) }); }
public IEnumerable<Tag> GetNewTags(Guid subject, Folder parentFolder, bool deepSearch) { var folderId = SharpBoxDaoSelector.ConvertId(parentFolder.ID); var fakeFolderId = parentFolder.ID.ToString(); var entryIDs = DbManager.ExecuteList(Query("files_thirdparty_id_mapping") .Select("hash_id") .Where(Exp.Like("id", fakeFolderId, SqlLike.StartWith))) .ConvertAll(x => x[0]); if (!entryIDs.Any()) return new List<Tag>(); var sqlQuery = new SqlQuery("files_tag ft") .Select("ft.name", "ft.flag", "ft.owner", "ftl.entry_id", "ftl.entry_type", "ftl.tag_count", "ft.id") .Distinct() .LeftOuterJoin("files_tag_link ftl", Exp.EqColumns("ft.tenant_id", "ftl.tenant_id") & Exp.EqColumns("ft.id", "ftl.tag_id")) .Where(Exp.Eq("ft.tenant_id", TenantID) & Exp.Eq("ftl.tenant_id", TenantID) & Exp.Eq("ft.flag", (int) TagType.New) & Exp.In("ftl.entry_id", entryIDs)); if (subject != Guid.Empty) sqlQuery.Where(Exp.Eq("ft.owner", subject)); var tags = DbManager.ExecuteList(sqlQuery).ConvertAll(r => new Tag { TagName = Convert.ToString(r[0]), TagType = (TagType) r[1], Owner = new Guid(r[2].ToString()), EntryId = MappingID(r[3]), EntryType = (FileEntryType) r[4], Count = Convert.ToInt32(r[5]), Id = Convert.ToInt32(r[6]) }); if (deepSearch) return tags; var folderFileIds = new[] {fakeFolderId} .Concat(GetFolderSubfolders(folderId).Select(x => MakeId(x))) .Concat(GetFolderFiles(folderId).Select(x => MakeId(x))); return tags.Where(tag => folderFileIds.Contains(tag.EntryId.ToString())); }
private List<IProviderInfo> GetProvidersInfoInternal(FolderType folderType) { var querySelect = new SqlQuery(TableTitle) .Select("id", "provider", "customer_title", "user_name", "password", "token", "user_id", "folder_type", "create_on") .Where("tenant_id", TenantID) .Where("folder_type", (int) folderType); if (folderType == FolderType.USER) querySelect = querySelect.Where(Exp.Eq("user_id", SecurityContext.CurrentAccount.ID.ToString())); return DbManager.ExecuteList(querySelect).ConvertAll<IProviderInfo>(ToProviderInfo); }
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; using (var db = GetDb()) { 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.AddDays(1).AddMinutes(-1)))); 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.AddDays(1).AddMinutes(-1)))); 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 SqlQuery CreateQueryFilter(SqlQuery query, TaskFilter filter, bool isAdmin) { if (filter.Follow) { var objects = new List<String>(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)).ToList(); } 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 (!string.IsNullOrEmpty(filter.SearchText)) { query.Where(Exp.Like("t.title", filter.SearchText, SqlLike.AnyWhere)); } 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 List<ResWord> GetListResWords(ResFile resFile, string to, string search) { using (var dbManager = new DbManager("tmresource")) { var sql = new SqlQuery("res_data") .Select("title", "fileid", "textValue", "description", "flag", "link") .InnerJoin("res_files", Exp.EqColumns("res_files.ID", "res_data.fileID")) .Where("moduleName", resFile.ModuleName) .Where("projectName", resFile.ProjectName) .Where("cultureTitle", to) .Where("flag != 4") .Where("resourceType", "text") .OrderBy("res_data.id", true); if (!String.IsNullOrEmpty(resFile.FileName)) sql.Where("resName", resFile.FileName); if (!String.IsNullOrEmpty(search)) sql.Where(Exp.Like("textvalue", search)); return dbManager.ExecuteList(sql).ConvertAll(r => GetWord(r)); } }
private Exp WhereConditional( ICollection<int> exceptIDs, String searchText, int contactID, bool? isClosed, IEnumerable<String> tags) { var conditions = new List<Exp>(); var ids = new List<int>(); if (!String.IsNullOrEmpty(searchText)) { searchText = searchText.Trim(); var keywords = searchText.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries) .ToArray(); if (keywords.Length > 0) { var modules = SearchDao.GetFullTextSearchModule(EntityType.Case, searchText); if (FullTextSearch.SupportModule(modules)) { ids = FullTextSearch.Search(modules); if (ids.Count == 0) return null; } else conditions.Add(BuildLike(new[] {"title"}, keywords)); } } if (contactID > 0) { var sqlQuery = new SqlQuery("crm_entity_contact") .Select("entity_id") .Where(Exp.Eq("contact_id", contactID) & Exp.Eq("entity_type", (int)EntityType.Case)); if (ids.Count > 0) sqlQuery.Where(Exp.In("entity_id", ids)); using (var db = GetDb()) { ids = db.ExecuteList(sqlQuery).Select(item => Convert.ToInt32(item[0])).ToList(); } if (ids.Count == 0) return null; } if (isClosed.HasValue) conditions.Add(Exp.Eq("is_closed", isClosed)); if (tags != null && tags.Any()) { ids = SearchByTags(EntityType.Case, ids.ToArray(), tags); if (ids.Count == 0) return null; } if (ids.Count > 0) { if (exceptIDs.Count > 0) { ids = ids.Except(exceptIDs).ToList(); if (ids.Count == 0) return null; } conditions.Add(Exp.In("id", ids)); } else if (exceptIDs.Count > 0) { conditions.Add(!Exp.In("id", exceptIDs.ToArray())); } if (conditions.Count == 0) return null; return conditions.Count == 1 ? conditions[0] : conditions.Aggregate((i, j) => i & j); }
public List<MailAlert> FindAlerts(IDbManager db, int tenant, string user, int mailboxId = -1, AlertTypes type = AlertTypes.Empty) { var searchQuery = new SqlQuery(MailAlertsTable.name) .Select(MailAlertsTable.Columns.id, MailAlertsTable.Columns.type, MailAlertsTable.Columns.id_mailbox, MailAlertsTable.Columns.data) .Where(GetUserWhere(user, tenant)); if (mailboxId > 0) searchQuery.Where(MailAlertsTable.Columns.id_mailbox, mailboxId); if (type != AlertTypes.Empty) searchQuery.Where(MailAlertsTable.Columns.type, (int)type); var foundAlerts = db.ExecuteList(searchQuery) .ConvertAll(x => new MailAlert { id = Convert.ToInt32(x[0]), type = (AlertTypes)x[1], id_mailbox = Convert.ToInt32(x[2]), data = (string)x[3] }); return foundAlerts; }
private int GetFoldersCount(object parentId, bool withSubfoldes) { var q = new SqlQuery("files_folder_tree").SelectCount().Where("parent_id", parentId); if (withSubfoldes) { q.Where(Exp.Gt("level", 0)); } else { q.Where("level", 1); } using (var dbManager = GetDb()) { return dbManager.ExecuteScalar<int>(q); } }
public List<int> GetTaskCount(List<int> projectId, TaskStatus? taskStatus, bool isAdmin) { var query = new SqlQuery(TasksTable + " t") .Select("t.project_id").SelectCount() .Where(Exp.In("t.project_id", projectId)) .Where("t.tenant_id", Tenant) .GroupBy("t.project_id"); if (taskStatus != null) { if (taskStatus == TaskStatus.Open) query.Where(!Exp.Eq("t.status", TaskStatus.Closed)); else query.Where("t.status", TaskStatus.Closed); } if (!isAdmin) { query.InnerJoin(ProjectsTable + " p", Exp.EqColumns("t.project_id", "p.id") & Exp.EqColumns("t.tenant_id", "p.tenant_id")) .LeftOuterJoin(TasksResponsibleTable + " ptr", Exp.EqColumns("t.tenant_id", "ptr.tenant_id") & Exp.EqColumns("t.id", "ptr.task_id") & Exp.Eq("ptr.responsible_id", CurrentUserID)) .LeftOuterJoin(ParticipantTable + " ppp", Exp.EqColumns("p.id", "ppp.project_id") & Exp.Eq("ppp.removed", false) & Exp.Eq("ppp.participant_id", CurrentUserID)) .Where(Exp.Eq("p.private", false) | !Exp.Eq("ptr.responsible_id", null) | (Exp.Eq("p.private", true) & !Exp.Eq("ppp.security", null) & !Exp.Eq("ppp.security & " + (int)ProjectTeamSecurity.Tasks, (int)ProjectTeamSecurity.Tasks))); } using (var db = new DbManager(DatabaseId)) { var result = db.ExecuteList(query); return projectId.ConvertAll( pid => { var res = result.Find(r => Convert.ToInt32(r[0]) == pid); return res == null ? 0 : Convert.ToInt32(res[1]); } ); } }
private static SqlQuery GetGroupQuery(int tenant, DateTime from) { var q = new SqlQuery("core_group").Select("id", "name", "parentid", "categoryid", "removed", "last_modified", "tenant"); if (tenant != Tenant.DEFAULT_TENANT) q.Where("tenant", tenant); if (from != default(DateTime)) q.Where(Exp.Ge("last_modified", from)); return q; }