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