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]));
}