using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Spaces;
using System.Web.Caching;

/// <summary>
/// SiteHelper 的摘要说明
/// </summary>
public static class  SiteHelper
{


    #region 获取最新图片
     public  static DataView  GetNewPhotos(int count)
    {
        string key = string.Format("bfor_{0}_newPhotos", count);
        DataView dv = HttpRuntime.Cache[key] as DataView;
        if (dv == null)
        {
            string sql="";
            switch (Config.GetConfig().DefaultProvider)
           { 
                case "MySql":
                    sql = string.Format(@"select  d.*,c.Name,c.Account from photo_Data d inner join photo_Categories c 
                    on c.ID=d.CategoryID order by d.Created desc limit 0,{0}", count);
                    break;
                case "SqlServer":
                    sql = string.Format(@"select top {0} d.*,c.[Name],c.[Account] from photo_Data d inner join photo_Categories c 
                    on c.ID=d.CategoryID order by d.Created desc", count);
                    break;
                case "Access":
                    sql = string.Format(@"select top {0} d.*,c.[Name],c.[Account] from photo_Data d inner join photo_Categories c 
                    on c.ID=d.CategoryID order by d.Created desc", count);
                    break;

            }  
            dv = SpacesDataProvider.Instance().GetDataView(sql);
            HttpRuntime.Cache.Add(key, dv, null, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.Default, null);

        }
        return dv;
    }
    #endregion

  
    #region 最新主题
    public static DataView GetTopicNumsTop(int count)
    {


        string key = string.Format("bfor_{0}_TopicNumsTop", count);
        DataView dv = HttpRuntime.Cache[key] as DataView;
        if (dv == null)
        {

            string sql = "";
            switch (Config.GetConfig().DefaultProvider)
            {
                case "MySql":
                    sql = string.Format(@"SELECT  c.BlogID,count(c.ID) as TopicNums,uc.Account,uc.Title FROM 
                        Blog_Content c 
                        inner join blog_Config bc on (bc.blogID=c.BlogID) 
                        inner join User_UserConfig uc     on (uc.Account=bc.Account)
                        where c.PostType=1 group by c.BlogID,uc.Account,uc.Title order by TopicNums desc limit 0,{0}", count);
                    break;
                case "SqlServer":
                    sql = string.Format(@"SELECT top {0} c.[BlogID],count(c.ID) as TopicNums,uc.Account,uc.Title FROM 
                        [Blog_Content] c 
                        inner join [blog_Config] bc on (bc.blogID=c.BlogID) 
                        inner join [User_UserConfig] uc     on (uc.Account=bc.Account)
                        where c.PostType=1 group by c.[BlogID],uc.Account,uc.Title order by TopicNums desc", count);
                    break;
                case "Access":
                    sql = string.Format(@"SELECT top {0} c.[BlogID],count(c.ID) as TopicNums,uc.Account,uc.Title FROM 
                        ([Blog_Content] c 
                        inner join [blog_Config] bc on (bc.blogID=c.BlogID) )
                        inner join [User_UserConfig] uc     on (uc.Account=bc.Account)
                        where c.PostType=1 group by c.[BlogID],uc.Account,uc.Title order by count(c.ID) desc", count);
                    break;

            }
 
            dv = SpacesDataProvider.Instance().GetDataView(sql);
            HttpRuntime.Cache.Add(key, dv, null, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.Default, null);

        }
        return dv;

    }
    #endregion

    #region 相册
    public static DataView GetHitPhotos(int count)
    {
        string key = string.Format("bfor_{0}_HitPhotos", count);
        DataView dv = HttpRuntime.Cache[key] as DataView;
        if (dv == null)
        {
            string sql = "";
            switch (Config.GetConfig().DefaultProvider)
            {
                case "MySql":
                    sql = string.Format(@"select * from photo_Data  d join photo_Categories c on (c.ID=d.CategoryID)
                     where c.ID in( select CategoryID from Photo_Data group by CategoryID order by count(PhotoID) desc)
                    and d.PhotoID=c.MainPhotoID limit 0,{0}", count);
                    break;
                case "SqlServer":
                    sql = string.Format(@"select * from photo_Data  d join photo_Categories c on (c.ID=d.CategoryID) where c.ID in( select top {0} CategoryID from Photo_Data group by CategoryID order by count(PhotoID) desc)
            and d.PhotoID=c.MainPhotoID", count);
                    break;
                case "Access":
                    sql = string.Format(@"select * from photo_Data  d inner join photo_Categories c on (c.ID=d.CategoryID) where c.ID in( select top {0} CategoryID from Photo_Data group by CategoryID order by count(PhotoID) desc)
            and d.PhotoID=c.MainPhotoID", count);
                    break;

            }
            dv = SpacesDataProvider.Instance().GetDataView(string.Format(sql, count));
            HttpRuntime.Cache.Insert(key, dv, null, DateTime.Now.AddHours(3), TimeSpan.Zero);
        }
        return dv;
    }
    #endregion

    #region 最近更新相册的空间
    public static DataView GetNewPhotoSpaces(int count)
    {

        string key = string.Format("bfor_{0}_NewPhotoSpaces", count);
        DataView dv = HttpRuntime.Cache[key] as DataView;
        if (dv == null)
        {
            string sql = "";
            switch (Config.GetConfig().DefaultProvider)
            {
                case "MySql":
                    sql = string.Format(@"SELECT    pc.Account,uc.Title FROM 
            Photo_Data d 
            inner join photo_Categories pc on (d.CategoryID=pc.ID) 
            inner join User_UserConfig uc     on (uc.Account=pc.Account)
            group by pc.Account,uc.Title
            order by Max(d.Created) desc limit 0,{0}", count);
                    break;
                case "SqlServer":
                    sql = string.Format(@"SELECT top {0}  pc.Account,uc.Title FROM 
            [Photo_Data] d 
            inner join [photo_Categories] pc on (d.CategoryID=pc.ID) 
            inner join [User_UserConfig] uc     on (uc.Account=pc.Account)
            group by pc.Account,uc.Title
            order by Max(d.Created) desc", count);
                    break;
                case "Access":
                    sql = string.Format(@"SELECT top {0}  pc.Account,uc.Title FROM 
           ( [Photo_Data] d 
            inner join [photo_Categories] pc on (d.CategoryID=pc.ID) )
            inner join [User_UserConfig] uc     on (uc.Account=pc.Account)
            group by pc.Account,uc.Title
            order by Max(d.Created) desc", count);
                    break;

            }

            dv = SpacesDataProvider.Instance().GetDataView(string.Format(sql, count));
            HttpRuntime.Cache.Add(key, dv, null, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.Default, null);

        }
        return dv;

    }
    #endregion

    #region 热门文章
    public static DataView GetHitTopicTop(int count)
    {
        string key = string.Format("bfor_{0}_tt_HitTopic", count);
        DataView dv = HttpRuntime.Cache[key] as DataView;
        if (dv == null)
        {
            string sql = "";
            switch (Config.GetConfig().DefaultProvider)
            {
                case "MySql":
                    sql = string.Format(@"SELECT   t.FeedBackCount*3+t.WebCount as hits,t.ID,t.title,u.Account,u.NickName  
                    FROM Blog_Content t 
                    inner join blog_Config c on (t.BlogID=c.BlogID)
                    inner join Users u on (u.Account=c.Account)
                    order by hits desc limit 0,{0}", count);
                    break;
                case "SqlServer":
                    sql = string.Format(@"SELECT top {0} t.[FeedBackCount]*3+t.[WebCount] as hits,t.ID,t.title,u.Account,u.NickName  
                    FROM [Blog_Content] t 
                    inner join [blog_Config] c on (t.BlogID=c.BlogID)
                    inner join [Users] u on (u.Account=c.Account)
                    order by hits desc", count);
                    break;
                case "Access":
                    sql = string.Format(@"SELECT top {0} t.[FeedBackCount]*3+t.[WebCount] as hits,t.ID,t.title,u.Account,u.NickName  
                    FROM ([Blog_Content] t 
                    inner join [blog_Config] c on (t.BlogID=c.BlogID))
                    inner join [Users] u on (u.Account=c.Account)
                    order by t.[FeedBackCount]*3+t.[WebCount] desc", count);
                    break;

            }
       
            dv = SpacesDataProvider.Instance().GetDataView(string.Format(sql, count));
            HttpRuntime.Cache.Add(key, dv, null, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.Default, null);

        }
        return dv;

    }
    #endregion
    #region 热门文章
    public static DataView GetTopicHitTop(int count)
    {


        string key = string.Format("bfor_{0}_TopicHitTop_", count);
        DataView dv = HttpRuntime.Cache[key] as DataView;
        if (dv == null)
        {
            string sql = "";
            switch (Config.GetConfig().DefaultProvider)
            {
                case "MySql":
                    sql = string.Format(@"SELECT   c.BlogID,sum(c.WebCount) as TopicHitCount,uc.Account,uc.Title FROM 
                    Blog_Content c 
                    inner join blog_Config bc on (bc.blogID=c.BlogID) 
                    inner join User_UserConfig uc     on (uc.Account=bc.Account)
                    where c.PostType=1 group by c.BlogID,uc.Account,uc.Title order by TopicHitCount desc limit 0,{0}", count);
                    break;
                case "SqlServer":
                    sql = string.Format(@"SELECT top {0} c.[BlogID],sum(c.WebCount) as TopicHitCount,uc.Account,uc.Title FROM 
                    [Blog_Content] c 
                    inner join [blog_Config] bc on (bc.blogID=c.BlogID) 
                    inner join [User_UserConfig] uc     on (uc.Account=bc.Account)
                     where c.PostType=1 group by c.[BlogID],uc.Account,uc.Title order by TopicHitCount desc", count);
                    break;
                case "Access":
                    sql = string.Format(@"SELECT top {0} c.[BlogID],sum(c.WebCount) as TopicHitCount,uc.Account,uc.Title FROM 
                   ( [Blog_Content] c 
                    inner join [blog_Config] bc on (bc.blogID=c.BlogID) )
                    inner join [User_UserConfig] uc     on (uc.Account=bc.Account)
                    where c.PostType=1 group by c.[BlogID],uc.Account,uc.Title order by sum(c.WebCount)  desc", count);
                    break;

            }
 
            dv = SpacesDataProvider.Instance().GetDataView(sql);
            HttpRuntime.Cache.Add(key, dv, null, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.Default, null);

        }
        return dv;

    }
    #endregion

    #region 评论排行
    public static DataView GetFeedBackTop(int count)
    {
        string key = string.Format("bfor_{0}_FeedBackTop_", count);
        DataView dv = HttpRuntime.Cache[key] as DataView;
        if (dv == null)
        {
            string sql = "";
            switch (Config.GetConfig().DefaultProvider)
            {
                case "MySql":
                    sql = string.Format(@"SELECT   c.BlogID,sum(c.FeedBackCount) as FeedBackCount,uc.Account,uc.Title FROM 
                    Blog_Content c 
                    inner join blog_Config bc on (bc.blogID=c.BlogID) 
                    inner join User_UserConfig uc     on (uc.Account=bc.Account)
                    where c.PostType=1 group by c.BlogID,uc.Account,uc.Title order by FeedBackCount desc limit 0,{0}", count);
                    break;
                case "SqlServer":
                    sql = string.Format(@"SELECT top {0} c.[BlogID],sum(c.FeedBackCount) as FeedBackCount,uc.Account,uc.Title FROM 
                    [Blog_Content] c 
                    inner join [blog_Config] bc on (bc.blogID=c.BlogID) 
                    inner join [User_UserConfig] uc     on (uc.Account=bc.Account)
                    where c.PostType=1 group by c.[BlogID],uc.Account,uc.Title order by FeedBackCount desc", count);
                    break;
                case "Access":
                    sql = string.Format(@"SELECT top {0} c.[BlogID],sum(c.FeedBackCount) as FeedBackCount,uc.Account,uc.Title FROM 
                   ( [Blog_Content] c 
                    inner join [blog_Config] bc on (bc.blogID=c.BlogID) )
                    inner join [User_UserConfig] uc     on (uc.Account=bc.Account)
                    where c.PostType=1 group by c.[BlogID],uc.Account,uc.Title order by sum(c.FeedBackCount) desc", count);
                    break;

            }
       
            dv = SpacesDataProvider.Instance().GetDataView(sql);
            HttpRuntime.Cache.Add(key, dv, null, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.Default, null);

        }
        return dv;

    }
    #endregion

    #region 相册图片数排行
    public static DataView GetPhotoCountTop(int count)
    {
        string key = string.Format("bfor_{0}_PhotoCountTop_", count);
        DataView dv = HttpRuntime.Cache[key] as DataView;
        if (dv == null)
        {
            string sql = "";
            switch (Config.GetConfig().DefaultProvider)
            {
                case "MySql":
                    sql = string.Format(@"SELECT  count(d.PhotoID) as PhotoCount,pc.Account,uc.Title FROM 
                    Photo_Data d 
                    inner join photo_Categories pc on (d.CategoryID=pc.ID) 
                    inner join User_UserConfig uc     on (uc.Account=pc.Account)
                    group by pc.Account,uc.Title order by PhotoCount desc limit 0,{0}", count);
                    break;
                case "SqlServer":
                    sql = string.Format(@"SELECT top {0} count(d.PhotoID) as PhotoCount,pc.Account,uc.Title FROM 
                    [Photo_Data] d 
                    inner join [photo_Categories] pc on (d.CategoryID=pc.ID) 
                    inner join [User_UserConfig] uc     on (uc.Account=pc.Account)
                    group by pc.[Account],uc.Title order by PhotoCount desc", count);
                    break;
                case "Access":
                    sql = string.Format(@"SELECT top {0} count(d.PhotoID) as PhotoCount,pc.Account,uc.Title FROM 
                    ([Photo_Data] d 
                    inner join [photo_Categories] pc on (d.CategoryID=pc.ID) )
                    inner join [User_UserConfig] uc     on (uc.Account=pc.Account)
                    group by pc.[Account],uc.Title order by count(d.PhotoID) desc", count);
                    break;

            }
     
            dv = SpacesDataProvider.Instance().GetDataView(sql);
            HttpRuntime.Cache.Add(key, dv, null, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.Default, null);

        }
        return dv;

    }
    #endregion

    #region 更新文件的空间
    public static DataView GetFileUpdatedSpaces(int count)
    {

        string key = string.Format("bfor_{0}_FileUpdated_", count);
        DataView dv = HttpRuntime.Cache[key] as DataView;
        if (dv == null)
        {
            string sql = "";
            switch (Config.GetConfig().DefaultProvider)
            {
                case "MySql":
                    sql = string.Format(@"SELECT   c.Account,c.Title FROM 
            NetDisk_Resource r 
            inner join NetDisk_Directory d on (r.DirectoryID=d.DirectoryID) 
            inner join User_UserConfig c    on (c.Account=d.Account)
            group by c.Account,c.Title
            order by Max(d.Created) desc limit 0,{0}", count);
                    break;
                case "SqlServer":
                    sql = string.Format(@"SELECT top {0}  c.Account,c.Title FROM 
            [NetDisk_Resource] r 
            inner join [NetDisk_Directory] d on (r.DirectoryID=d.DirectoryID) 
            inner join [User_UserConfig] c    on (c.Account=d.Account)
            group by c.Account,c.Title
            order by Max(d.Created) desc", count);
                    break;
                case "Access":
                    sql = string.Format(@"SELECT top {0}  c.Account,c.Title FROM 
           ( [NetDisk_Resource] r 
            inner join [NetDisk_Directory] d on (r.DirectoryID=d.DirectoryID) )
            inner join [User_UserConfig] c    on (c.Account=d.Account)
            group by c.Account,c.Title
            order by Max(d.Created) desc", count);
                    break;

            }

            dv = SpacesDataProvider.Instance().GetDataView(string.Format(sql, count));
            HttpRuntime.Cache.Add(key, dv, null, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.Default, null);

        }
        return dv;

    }
    #endregion

    #region 新图片 标签
    public static DataView GetNewPhotos(string keyName, int count)
    {
        string key = string.Format("bfor__Tag_{0}_Photo_{1}", keyName, count);
        DataView dv = HttpRuntime.Cache[key] as DataView;
        if (dv == null)
        {
            string sql = "";
            switch (Config.GetConfig().DefaultProvider)
            {
                case "MySql":
                    sql = string.Format(@"select d.*,c.`Name`,c.`Account` from photo_Data d inner join photo_Categories c 
on c.ID=d.CategoryID 
where d.PhotoID in
(select (pt.EntityID) from `tbl_Tags` tag 
inner join `tbl_EntityToTag` pt 
on tag.TagID=pt.TagID and tag.TagName='{1}' and pt.TagType=3) limit 0,{0}", count, Utils.SqlInput(keyName));
                    break;
                case "SqlServer":
                    sql = string.Format(@"select top {0} d.*,c.[Name],c.[Account] from photo_Data d inner join photo_Categories c 
on c.ID=d.CategoryID 
where d.PhotoID in
(select (pt.EntityID) from [tbl_Tags] tag 
inner join [tbl_EntityToTag] pt 
on tag.TagID=pt.TagID and tag.TagName='{1}' and pt.TagType=3)", count, Utils.SqlInput(keyName));
                    break;
                case "Access":
                    sql = string.Format(@"select top {0} d.*,c.[Name],c.[Account] from photo_Data d inner join photo_Categories c 
on c.ID=d.CategoryID 
where d.PhotoID in
(select (pt.EntityID) from [tbl_Tags] tag 
inner join [tbl_EntityToTag] pt 
on (tag.TagID=pt.TagID and tag.TagName='{1}' and pt.TagType=3))", count, Utils.SqlInput(keyName));
                    break;

            }
           
            dv = SpacesDataProvider.Instance().GetDataView(sql);
            HttpRuntime.Cache.Add(key, dv, null, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.Default, null);

        }
        return dv;

    }
    #endregion

    #region 文件 标签
    public static DataView GetTagFiles(string keyName, int count)
    {
        string key = string.Format("bfor__Tag_{0}_Files_{1}", keyName, count);
        DataView dv = HttpRuntime.Cache[key] as DataView;
        if (dv == null)
        {
            string sql = "";
            switch (Config.GetConfig().DefaultProvider)
            {
                case "MySql":
                    sql = string.Format(@"SELECT   u.Account,f.*,d.Directory,d.`Password`,d.Attribute as FolderAttribute,d.limits FROM `NetDisk_Resource` f join
`NetDisk_Directory` d on( f.DirectoryID=d.DirectoryID) inner join Users u on u.Account =D.Account
where d.Password=''
and f.FileID in
(select (pt.EntityID) from `tbl_Tags` tag 
inner join `tbl_EntityToTag` pt 
on tag.TagID=pt.TagID and tag.TagName='{1}' and pt.TagType=2) limit 0,{0}", count, Utils.SqlInput(keyName));
                    break;
                case "SqlServer":
                    sql = string.Format(@"SELECT top {0} u.Account,f.*,d.Directory,d.[Password],d.Attribute as FolderAttribute,d.limits FROM [NetDisk_Resource] f join
[NetDisk_Directory] d on( f.DirectoryID=d.DirectoryID) inner join Users u on u.Account =D.Account
where d.Password=''
and f.FileID in
(select (pt.EntityID) from [tbl_Tags] tag 
inner join [tbl_EntityToTag] pt 
on tag.TagID=pt.TagID and tag.TagName='{1}' and pt.TagType=2)", count, Utils.SqlInput(keyName));
                    break;
                case "Access":
                    sql = string.Format(@"SELECT top {0} u.Account,f.*,d.Directory,d.[Password],d.Attribute as FolderAttribute,d.limits FROM( [NetDisk_Resource] f inner join
[NetDisk_Directory] d on( f.DirectoryID=d.DirectoryID)) inner join Users u on u.Account =D.Account
where d.Password=''
and f.FileID in
(select (pt.EntityID) from [tbl_Tags] tag 
inner join [tbl_EntityToTag] pt 
on (tag.TagID=pt.TagID and tag.TagName='{1}' and pt.TagType=2))", count, Utils.SqlInput(keyName));
                    break;

            }
          
            dv = SpacesDataProvider.Instance().GetDataView(string.Format(sql, keyName, count));
            HttpRuntime.Cache.Add(key, dv, null, DateTime.Now.AddHours(3), TimeSpan.Zero, CacheItemPriority.Default, null);

        }
        return dv;

    }
    #endregion
}
