public Dictionary<int, List<String>> GetEntitiesTags(EntityType entityType) { var result = new Dictionary<int, List<String>>(); var sqlQuery = new SqlQuery("crm_entity_tag") .Select("entity_id", "title") .LeftOuterJoin("crm_tag", Exp.EqColumns("id", "tag_id")) .Where(Exp.Eq("crm_tag.entity_type", (int)entityType) & Exp.Eq("crm_tag.tenant_id", TenantID)) .OrderBy("entity_id", true) .OrderBy("title", true); DbManager.ExecuteList(sqlQuery).ForEach(row => { var entityID = Convert.ToInt32(row[0]); var tagTitle = Convert.ToString(row[1]); if (!result.ContainsKey(entityID)) result.Add(entityID, new List<String> { tagTitle }); else result[entityID].Add(tagTitle); }); return result; }
public DBResourceReader(string fileName, CultureInfo culture) { language = culture.Name; Data = new Hashtable(); if (language == "") language = "Neutral"; using (var dbManager = new DbManager("tmresource")) { var sql = new SqlQuery("res_data") .Select("res_data.title", "textValue") .InnerJoin("res_files", Exp.EqColumns("res_files.id", "res_data.fileID")) .LeftOuterJoin("res_cultures", Exp.EqColumns("res_cultures.title", "res_data.cultureTitle")) .Where("ResName", fileName) .Where("res_cultures.Title", language); var list = dbManager.ExecuteList(sql); foreach (var t in list) { Data.Add(t[0], t[1]); } } }
public override List<UsageSpaceStatItem> GetStatData() { if (!DbRegistry.IsDatabaseRegistered(FileConstant.DatabaseId)) { DbRegistry.RegisterDatabase(FileConstant.DatabaseId, ConfigurationManager.ConnectionStrings[FileConstant.DatabaseId]); } using (var filedb = new DbManager(FileConstant.DatabaseId)) { var q = new SqlQuery("files_file f") .Select("b.right_node") .SelectSum("f.content_length") .InnerJoin("files_folder_tree t", Exp.EqColumns("f.folder_id", "t.folder_id")) .InnerJoin("files_bunch_objects b", Exp.EqColumns("t.parent_id", "b.left_node")) .Where("b.tenant_id", TenantProvider.CurrentTenantID) .Where(Exp.Like("b.right_node", "crm/crm_common/", SqlLike.StartWith)) .GroupBy(1); return filedb.ExecuteList(q) .Select(r => new UsageSpaceStatItem { Name = Resources.CRMCommonResource.ProductName, SpaceUsage = Convert.ToInt64(r[1]), Url = VirtualPathUtility.ToAbsolute(PathProvider.StartURL()) }) .ToList(); } }
protected List<int> SearchByTags(EntityType entityType, int[] exceptIDs, IEnumerable<String> tags) { if (tags == null || !tags.Any()) throw new ArgumentException(); var tagIDs = new List<int>(); foreach (var tag in tags) tagIDs.Add(DbManager.ExecuteScalar<int>(Query("crm_tag") .Select("id") .Where(Exp.Eq("entity_type", (int)entityType ) & Exp.Eq("title", tag)))); var sqlQuery = new SqlQuery("crm_entity_tag") .Select("entity_id") .Select("count(*) as count") .GroupBy("entity_id") .Having(Exp.Eq("count", tags.Count())); if (exceptIDs != null && exceptIDs.Length > 0) sqlQuery.Where(Exp.In("entity_id", exceptIDs) & Exp.Eq("entity_type", (int)entityType)); else sqlQuery.Where(Exp.Eq("entity_type", (int)entityType)); sqlQuery.Where(Exp.In("tag_id", tagIDs)); return DbManager.ExecuteList(sqlQuery).ConvertAll(row => Convert.ToInt32(row[0])); }
public IEnumerable<AzRecord> GetAces(int tenant, DateTime from) { // row with tenant = -1 - common for all tenants, but equal row with tenant != -1 escape common row for the portal var q = new SqlQuery("core_acl") .Select("subject", "action", "object", "acetype") .Where(Exp.Eq("tenant", Tenant.DEFAULT_TENANT)); var commonAces = ExecList(q) .ConvertAll(r => ToAzRecord(r, tenant)) .ToDictionary(a => string.Concat(a.Tenant.ToString(), a.SubjectId.ToString(), a.ActionId.ToString(), a.ObjectId)); q = new SqlQuery("core_acl") .Select("subject", "action", "object", "acetype") .Where(Exp.Eq("tenant", tenant)); var tenantAces = ExecList(q) .ConvertAll(r => new AzRecord(new Guid((string)r[0]), new Guid((string)r[1]), (AceType)Convert.ToInt32(r[3]), string.Empty.Equals(r[2]) ? null : (string)r[2]) { Tenant = tenant }); // remove excaped rows foreach (var a in tenantAces.ToList()) { var key = string.Concat(a.Tenant.ToString(), a.SubjectId.ToString(), a.ActionId.ToString(), a.ObjectId); if (commonAces.ContainsKey(key)) { var common = commonAces[key]; commonAces.Remove(key); if (common.Reaction == a.Reaction) { tenantAces.Remove(a); } } } return commonAces.Values.Concat(tenantAces); }
public override List<UsageSpaceStatItem> GetStatData() { if (!DbRegistry.IsDatabaseRegistered(MailDatabaseId)) { DbRegistry.RegisterDatabase(MailDatabaseId, ConfigurationManager.ConnectionStrings[MailDatabaseId]); } using (var mail_db = new DbManager(MailDatabaseId)) { var query = new SqlQuery("mail_attachment a") .InnerJoin("mail_mail m", Exp.EqColumns("a.id_mail", "m.id")) .Select("m.id_user") .Select("sum(a.size) as size") .Where("m.tenant", TenantProvider.CurrentTenantID) .Where("a.need_remove", 0) .GroupBy(1) .OrderBy(2, false); return mail_db.ExecuteList(query) .Select(r => { var user_id = new Guid(Convert.ToString(r[0])); var user = CoreContext.UserManager.GetUsers(user_id); var item = new UsageSpaceStatItem { Name = DisplayUserSettings.GetFullUserName(user, false), ImgUrl = UserPhotoManager.GetSmallPhotoURL(user.ID), Url = CommonLinkUtility.GetUserProfile(user.ID), SpaceUsage = Convert.ToInt64(r[1]) }; return item; }) .ToList(); } }
public override List<UsageSpaceStatItem> GetStatData() { using (var filedb = new DbManager(FileConstant.DatabaseId)) using (var projdb = new DbManager(PROJECTS_DBID)) { var q = new SqlQuery("files_file f") .Select("b.right_node") .SelectSum("f.content_length") .InnerJoin("files_folder_tree t", Exp.EqColumns("f.folder_id", "t.folder_id")) .InnerJoin("files_bunch_objects b", Exp.EqColumns("t.parent_id", "b.left_node")) .Where("b.tenant_id", TenantProvider.CurrentTenantID) .Where(Exp.Like("b.right_node", "projects/project/", SqlLike.StartWith)) .GroupBy(1); var sizes = filedb.ExecuteList(q) .Select(r => new {ProjectId = Convert.ToInt32(((string) r[0]).Substring(17)), Size = Convert.ToInt64(r[1])}) .GroupBy(r => r.ProjectId) .ToDictionary(g => g.Key, g => g.Sum(a => a.Size)); q = new SqlQuery("projects_projects") .Select("id", "title") .Where("tenant_id", TenantProvider.CurrentTenantID) .Where(Exp.In("id", sizes.Keys)); return projdb.ExecuteList(q) .Select(r => new UsageSpaceStatItem { Name = Convert.ToString(r[1]), SpaceUsage = sizes[Convert.ToInt32(r[0])], Url = String.Concat(PathProvider.BaseAbsolutePath, "projects.aspx?prjID=" + Convert.ToInt32(r[0])) }) .OrderByDescending(i => i.SpaceUsage) .ToList(); } }
private IEnumerable<Tag> SelectTagByQuery(SqlQuery q) { using (var DbManager = GetDbManager()) { return DbManager.ExecuteList(q).ConvertAll(r => ToTag(r)); } }
public override List<SpaceUsageStatManager.UsageSpaceStatItem> GetStatData() { var data = new List<SpaceUsageStatManager.UsageSpaceStatItem>(); if (!DbRegistry.IsDatabaseRegistered(FILES_DBID)) DbRegistry.RegisterDatabase(FILES_DBID, ConfigurationManager.ConnectionStrings[FILES_DBID]); using (var filedb = new DbManager(FILES_DBID)) { var q = new SqlQuery("files_file f") .Select("b.right_node") .SelectSum("f.content_length") .InnerJoin("files_folder_tree t", Exp.EqColumns("f.folder_id", "t.folder_id")) .InnerJoin("files_bunch_objects b", Exp.EqColumns("t.parent_id", "b.left_node")) .Where("b.tenant_id", CoreContext.TenantManager.GetCurrentTenant().TenantId) .Where(Exp.Like("b.right_node", "crm/crm_common/", SqlLike.StartWith)) .GroupBy(1); var size = filedb.ExecuteList(q).Select(r => Convert.ToInt64(r[1])).FirstOrDefault(); data.Add(new UsageSpaceStatItem() { Name = Resources.CRMCommonResource.ProductName, SpaceUsage = size, Url = VirtualPathUtility.ToAbsolute(PathProvider.StartURL()) }); } return data; }
public IPGeolocationInfo GetIPGeolocation(string ip) { try { var ipformatted = FormatIP(ip); using (var db = new DbManager(dbid)) { var q = new SqlQuery("dbip_location") .Select("ip_start", "ip_end", "country", "city", "timezone_offset", "timezone_name") .Where(Exp.Le("ip_start", ipformatted)) .OrderBy("ip_start", false) .SetMaxResults(1); return db .ExecuteList(q) .Select(r => new IPGeolocationInfo() { IPStart = Convert.ToString(r[0]), IPEnd = Convert.ToString(r[1]), Key = Convert.ToString(r[2]), City = Convert.ToString(r[3]), TimezoneOffset = Convert.ToDouble(r[4]), TimezoneName = Convert.ToString(r[5]) }) .SingleOrDefault(i => ipformatted.CompareTo(i.IPEnd) <= 0) ?? IPGeolocationInfo.Default; } } catch (Exception error) { log.Error(error); } return IPGeolocationInfo.Default; }
public long GetCount(string key) { using (DbManager dbMan = GetManager()) { SqlQuery query = new SqlQuery(CommentTable).Select("count(id)").Where("comment_key", key); return dbMan.ExecuteScalar<long>(query); } }
public Comment Get(long id) { using (DbManager dbMan = GetManager()) { SqlQuery query = new SqlQuery(CommentTable).Select(SelectFields).Where("id", id); return dbMan.ExecuteList(query).Select(x => ToComment(x)).SingleOrDefault(); } }
private static TenantServerDto GetFreeServer(IDbManager db) { if (db == null) throw new ArgumentNullException("db"); TenantServerDto serverDto; var serversQuery = new SqlQuery(ServerTable.name) .Select(ServerTable.Columns.id) .Select(ServerTable.Columns.connection_string) .Select(ServerTable.Columns.mx_record) .Select(ServerTable.Columns.server_type) .Select(ServerTable.Columns.smtp_settings_id) .Select(ServerTable.Columns.imap_settings_id) .Where(Exp.Gt(ServerTable.Columns.server_type, 0)); var servers = db.ExecuteList(serversQuery) .Select(r => r.ToTenantServerDto()) .ToList(); if (servers.Count > 1) { const string mst_alias = "mst"; const string mb_alias = "mb"; var serverXmailboxes = new Dictionary<int, int>(); foreach (var tenantServerDto in servers) { var serverMailboxesQuery = new SqlQuery(TenantXServerTable.name.Alias(mst_alias)) .InnerJoin(MailboxTable.name.Alias(mb_alias), Exp.EqColumns(TenantXServerTable.Columns.id_tenant.Prefix(mst_alias), MailboxTable.Columns.id_tenant.Prefix(mb_alias))) .SelectCount() .Where(MailboxTable.Columns.is_teamlab_mailbox.Prefix(mb_alias), true) .Where(MailboxTable.Columns.is_removed.Prefix(mb_alias), false) .Where(TenantXServerTable.Columns.id_server.Prefix(mst_alias), tenantServerDto.id) .GroupBy(TenantXServerTable.Columns.id_server.Prefix(mst_alias)); var count = db.ExecuteScalar<int>(serverMailboxesQuery); serverXmailboxes.Add(tenantServerDto.id, count); } var lightestServerId = serverXmailboxes.Aggregate((l, r) => l.Value < r.Value ? l : r).Key; serverDto = servers.First(s => s.id == lightestServerId); } else { serverDto = servers.FirstOrDefault(); } return serverDto; }
public override List<UsageSpaceStatItem> GetStatData() { if (!DbRegistry.IsDatabaseRegistered(PROJECTS_DBID)) { DbRegistry.RegisterDatabase(PROJECTS_DBID, ConfigurationManager.ConnectionStrings[PROJECTS_DBID]); } if (!DbRegistry.IsDatabaseRegistered(FileConstant.DatabaseId)) { DbRegistry.RegisterDatabase(FileConstant.DatabaseId, ConfigurationManager.ConnectionStrings[FileConstant.DatabaseId]); } using (var db = new DbManager(FileConstant.DatabaseId)) { var q = new SqlQuery("files_file f") .Select("f.create_by") .SelectSum("f.content_length") .InnerJoin("files_folder_tree t", Exp.EqColumns("f.folder_id", "t.folder_id")) .InnerJoin("files_bunch_objects b", Exp.EqColumns("t.parent_id", "b.left_node")) .Where("b.tenant_id", CoreContext.TenantManager.GetCurrentTenant().TenantId) .Where(Exp.Like("b.right_node", "files/my/", SqlLike.StartWith)) .GroupBy(1); var result = db.ExecuteList(q) .Select( r => new { User = CoreContext.UserManager.GetUsers(new Guid((string) r[0])), Size = Convert.ToInt64(r[1]) }) .GroupBy(r => r.User).ToDictionary(g => g.Key, g => g.Sum(a => a.Size)); q = new SqlQuery("files_file f") .SelectSum("f.content_length") .InnerJoin("files_folder_tree t", Exp.EqColumns("f.folder_id", "t.folder_id")) .InnerJoin("files_bunch_objects b", Exp.EqColumns("t.parent_id", "b.left_node")) .Where("b.tenant_id", CoreContext.TenantManager.GetCurrentTenant().TenantId) .Where("b.right_node", "files/common/"); var common = db.ExecuteScalar<long>(q); if (result.ContainsKey(Constants.LostUser)) { result[Constants.LostUser] += common; } else { result.Add(Constants.LostUser, common); } return result.Select(i => new UsageSpaceStatItem { Name = i.Key.Equals(Constants.LostUser) ? FilesUCResource.CorporateFiles : i.Key.DisplayUserName(false), ImgUrl = i.Key.Equals(Constants.LostUser) ? PathProvider.GetImagePath("corporatefiles_big.png") : i.Key.GetSmallPhotoURL(), Url = i.Key.Equals(Constants.LostUser) ? PathProvider.GetFolderUrl(Global.FolderCommon, false, null) : i.Key.GetUserProfilePageURL(), SpaceUsage = i.Value }) .OrderByDescending(t => t.SpaceUsage).ToList(); } }
public void QueryTtest() { var query = new SqlQuery("Table1 t1") .From(new SqlQuery("Table2").Select("Id"), "t2") .Select("t1.Name") .Where(Exp.EqColumns("t1.Id", "t2.Id")); Assert.AreEqual("select t1.Name from Table1 t1, (select Id from Table2) as t2 where t1.Id = t2.Id", query.ToString()); }
public ModuleInfo(string name) { if (string.IsNullOrEmpty(name)) throw new ArgumentNullException("name"); Name = name; query = new SqlQuery(Main) .SetFirstResult(0) .SetMaxResults(Limit - 1); match = new MatchSphinx(); }
public IEnumerable<string> GetLinkedObjectsByHashId(string hashid) { //Retrieve by uinque id using (var db = new DbManager(dbid)) { var query = new SqlQuery(LinkTable) .Select("id").Where("uid", hashid).Where(!Exp.Eq("provider", string.Empty)); return db.ExecuteList(query).ConvertAll(x => (string)x[0]); } }
public IEnumerable<LoginProfile> GetLinkedProfiles(string obj) { //Retrieve by uinque id using (var db = new DbManager(dbid)) { var query = new SqlQuery(LinkTable) .Select("profile").Where("id", obj); return db.ExecuteList(query).ConvertAll(x => LoginProfile.CreateFromSerializedString((string)x[0])); } }
public static DateTime GetLastUpdate() { using (var dbManager = new DbManager("tmresource")) { var sql = new SqlQuery("res_files"); sql.SelectMax("LastUpdate"); return dbManager.ExecuteScalar<DateTime>(sql); } }
public int[] GetTagProjects(int tagID) { using (var db = new DbManager(DatabaseId)) { var query = new SqlQuery(ProjectTagTable) .Select("project_id") .Where("tag_id", tagID); return db.ExecuteList(query).ConvertAll(r => Convert.ToInt32(r[0])).ToArray(); } }
public static IEnumerable<ResCulture> GetCultures() { using (var dbManager = new DbManager("tmresource")) { var sql = new SqlQuery("res_cultures"); sql.Select(new[] {"title", "value", "available"}) .OrderBy("title", true); return dbManager.ExecuteList(sql).ConvertAll(r => GetCultureFromDB(r)); } }
public static IEnumerable<IPRestriction> Get(int tenant) { var q = new SqlQuery(ipRestrictionsTable + " r") .Select(ipRestrictionColumns.Select(x => "r." + x).ToArray()) .Where("r.tenant", tenant); using (var db = new DbManager(dbId)) { return db.ExecuteList(q).ConvertAll(ToAuditEvent); } }
public bool IsExist(EntityType entityType, String tagName, DbManager db) { var q = new SqlQuery("crm_tag") .Select("1") .Where("tenant_id", TenantID) .Where("entity_type", (int)entityType) .Where("lower(title)", tagName.ToLower()) .SetMaxResults(1); return db.ExecuteScalar<bool>(q); }
private IProviderInfo GetProviderInfoInernal(int linkId) { var querySelect = new SqlQuery(TableTitle) .Select("id", "provider", "customer_title", "user_name", "password", "token", "user_id", "folder_type", "create_on") .Where("id", linkId) .Where("tenant_id", TenantID); // .Where(Exp.Eq("user_id", SecurityContext.CurrentAccount.ID.ToString()) | Exp.Eq("folder_type", (int) FolderType.COMMON)); return DbManager.ExecuteList(querySelect).ConvertAll<IProviderInfo>(ToProviderInfo).Single(); }
public static DateTime GetLastTimeAggregate(string key) { var q = new SqlQuery("feed_last") .Select("last_date") .Where("last_key", key); using (var db = new DbManager(Constants.FeedDbId)) { var value = db.ExecuteScalar<DateTime>(q); return value != default(DateTime) ? value.AddSeconds(1) : value; } }
public override List<UsageSpaceStatItem> GetStatData() { if (!DbRegistry.IsDatabaseRegistered(FileConstant.DatabaseId)) { DbRegistry.RegisterDatabase(FileConstant.DatabaseId, ConfigurationManager.ConnectionStrings[FileConstant.DatabaseId]); } using (var db = new DbManager(FileConstant.DatabaseId)) { var myFiles = new SqlQuery("files_file f") .Select("f.create_by") .Select("sum(f.content_length) as size") .InnerJoin("files_folder_tree t", Exp.EqColumns("f.folder_id", "t.folder_id")) .InnerJoin("files_bunch_objects b", Exp.EqColumns("f.tenant_id", "b.tenant_id") & Exp.EqColumns("t.parent_id", "b.left_node")) .Where("b.tenant_id", TenantProvider.CurrentTenantID) .Where(Exp.Like("b.right_node", "files/my/", SqlLike.StartWith) | Exp.Like("b.right_node", "files/trash/", SqlLike.StartWith)) .GroupBy(1); var commonFiles = new SqlQuery("files_file f") .Select("'" + Constants.LostUser.ID + "'") .Select("sum(f.content_length) as size") .InnerJoin("files_folder_tree t", Exp.EqColumns("f.folder_id", "t.folder_id")) .InnerJoin("files_bunch_objects b", Exp.EqColumns("f.tenant_id", "b.tenant_id") & Exp.EqColumns("t.parent_id", "b.left_node")) .Where("b.tenant_id", TenantProvider.CurrentTenantID) .Where("b.right_node", "files/common/"); var query = myFiles.UnionAll(commonFiles); return db.ExecuteList(query) .GroupBy(r => CoreContext.UserManager.GetUsers(new Guid(Convert.ToString(r[0]))), r => Convert.ToInt64(r[1]), (user, items) => { var item = new UsageSpaceStatItem { SpaceUsage = items.Sum() }; if (user.Equals(Constants.LostUser)) { item.Name = FilesUCResource.CorporateFiles; item.ImgUrl = PathProvider.GetImagePath("corporatefiles_big.png"); item.Url = PathProvider.GetFolderUrl(Global.FolderCommon); } else { item.Name = user.DisplayUserName(false); item.ImgUrl = user.GetSmallPhotoURL(); item.Url = user.GetUserProfilePageURL(); } return item; }) .OrderByDescending(i => i.SpaceUsage) .ToList(); } }
public int[] GetTagProjects(string tagName) { using (var db = new DbManager(DatabaseId)) { var query = new SqlQuery(ProjectTagTable) .Select("project_id") .InnerJoin(TagsTable, Exp.EqColumns("id", "tag_id") & Exp.Eq("projects_tags.tenant_id", Tenant)) .Where(Exp.Eq("lower(title)", tagName.ToLower())) .Where("tenant_id", Tenant); return db.ExecuteList(query).ConvertAll(r => Convert.ToInt32(r[0])).ToArray(); } }
public override IEnumerable<Tuple<Feed, object>> GetFeeds(FeedFilter filter) { var query = new SqlQuery("crm_contact") .Select(ContactColumns().ToArray()) .Where("tenant_id", filter.Tenant) .Where(Exp.Between("create_on", filter.Time.From, filter.Time.To)); using (var db = new DbManager(DbId)) { var contacts = db.ExecuteList(query).ConvertAll(ToContact); return contacts.Select(c => new Tuple<Feed, object>(ToFeed(c), c)); } }
public DateTime GetTimeReaded(Guid user, string module, int tenant) { var query = new SqlQuery("feed_readed") .SelectMax("timestamp") .Where("tenant_id", tenant) .Where("user_id", user.ToString()) .Where("module", module); using (var db = GetDb()) { return db.ExecuteScalar<DateTime>(query); } }
public String GetTagsLinkCountJSON(EntityType entityType) { var sqlQuery = new SqlQuery("crm_tag tbl_tag") .SelectCount("tag_id") .LeftOuterJoin("crm_entity_tag", Exp.EqColumns("id", "tag_id")) .Where(Exp.Eq("tbl_tag.entity_type", (int)entityType) & Exp.Eq("tbl_tag.tenant_id", TenantID)) .OrderBy("title", true) .GroupBy("tbl_tag.id"); var queryResult = DbManager.ExecuteList(sqlQuery); return JavaScriptSerializer.Serialize(queryResult.ConvertAll(row => row[0])); }