using System;
using System.Collections.Generic;
//using System.Linq;
using System.Web;

using System.Text;
using System.Data;
using System.Data.OleDb;

/// <summary>
///前台使用的方法
/// </summary>
public class FrontFunc
{
    #region 获取网站设置信息 页面标题、关键字、描述等
    /// <summary>
    /// 获取网站设置信息 页面标题、关键字、描述等
    /// </summary>
    public static DataTable GetSiteconfig()
    {

        string strSql = "";
        strSql += " select * ";
        strSql += " from NT_Siteconfig";

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, strSql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;
    }
    #endregion

    #region 栏目相关方法

    #region 根据栏目名称取该栏目下属全部栏目
    /// <summary>
    /// 根据栏目名称取该栏目下属全部栏目
    /// </summary>
    public static DataTable GetClassByCreateClassName(string Num,string CreateClassName)
    {
        string Top = Num == "ALL" ? "" : " top " + Num;
        string strSql = "";
        strSql += " select " + Top + " t.[ID],t.[ClassName],t.[CreateClassId],a.ClassName as CreateClassName";
        strSql += " from NT_Class t";
        strSql += " left join NT_Class a on t.CreateClassId=a.ID";
        strSql += " where a.ClassName='"+CreateClassName+"'";
        strSql += " order by t.OrderNum asc";

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, strSql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;
    }
    #endregion

    #region 根据栏目ID取该栏目下属全部栏目
    /// <summary>
    /// 根据栏目ID取该栏目下属全部栏目
    /// </summary>
    public static DataTable GetClassByCreateClassID(string Num, int CreateClassID)
    {
        string Top = Num == "ALL" ? "" : " top " + Num;
        string strSql = "";
        strSql += " select " + Top + " t.[ID],t.[ClassName],t.[ClassIntro],t.[ClassPicture],a.ClassName as CreateClassName";
        strSql += " from NT_Class t";
        strSql += " left join NT_Class a on t.CreateClassId=a.ID";
        strSql += " where a.ID=" + CreateClassID;
        strSql += " order by t.OrderNum asc";

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, strSql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;
    }
    #endregion

    #region 根据栏目名称取ID
    /// <summary>
    /// 根据栏目名称取ID
    /// </summary>
    public static string GetClassIDByClassName(string ClassName)
    {
        string temp = "";
        string strSql = "";
        strSql += " select [ID] from NT_Class where [ClassName]='" + ClassName + "'";
        object obj = DbHelperOleDb.ExecuteScalar(CommandType.Text, strSql, null);
        if (obj != null)
        {
            temp = obj.ToString();
        }
        return temp;
    }
    #endregion

    #region 根据ClassName取栏目的信息（一般用于单页）
    /// <summary>
    /// 根据ClassName取栏目的信息（一般用于单页）
    /// </summary>
    /// <param name="ClassName"></param>
    /// <returns></returns>
    public static DataRow GetClassByClassName(string ClassName)
    {
        string str_sql = "";
        str_sql += " select [ID],[ClassName],[ClassType],[ClassIntro],[ClassPicture],";
        str_sql += " [ClassPath],[OrderNum],[CreateClassId],[PageTitle],";
        str_sql += " [PageKeyword],[PageDescription],[PagePicture],[PageContent] ";
        str_sql += " from NT_Class";
        str_sql += " where 1=1";
        str_sql += " and ClassName='" + ClassName + "'";

        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, str_sql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            return ds.Tables[0].Rows[0];
        }
        return null;
    }
    #endregion

    #region 根据ClassName取栏目的信息（一般用于单页）
    /// <summary>
    /// 根据ClassName取栏目的信息（一般用于单页）
    /// </summary>
    /// <param name="ClassName"></param>
    /// <returns></returns>
    public static DataRow GetClassByID(string ID)
    {
        string str_sql = "";
        str_sql += " select [ID],[ClassName],[ClassType],[ClassIntro],[ClassPicture],";
        str_sql += " [ClassPath],[OrderNum],[CreateClassId],[PageTitle],";
        str_sql += " [PageKeyword],[PageDescription],[PagePicture],[PageContent] ";
        str_sql += " from NT_Class";
        str_sql += " where 1=1";
        str_sql += " and  ID=" + ID + "";

        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, str_sql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            return ds.Tables[0].Rows[0];
        }
        return null;
    }

    public static DataTable GetClassByID(string Num, string strwhere)
    {
        string Top = Num == "ALL" ? "" : " top " + Num;
        string str_sql = "";
        str_sql += " select " + Top + " [ID],[ClassName],[ClassType],[ClassIntro],[ClassPicture],";
        str_sql += " [ClassPath],[OrderNum],[CreateClassId],[PageTitle],";
        str_sql += " [PageKeyword],[PageDescription],[PagePicture],[PageContent] ";
        str_sql += " from NT_Class";
        str_sql += " where 1=1";
        if (strwhere != "" && strwhere != null) {
            str_sql += " " + strwhere;
        }
        str_sql += " order by OrderNum asc,Id desc";

        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, str_sql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            return ds.Tables[0];
        }
        return null;
    }
    #endregion

    #region 根据ID来取Class附表的Property属性
    /// <summary>
    /// 根据ID来取Class附表的Property属性
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public static DataTable GetClassPropertyByID(string ID)
    {

        string sql_GetData = "";
        sql_GetData += " select [ID],[ClassId],";
        sql_GetData += " (select top 1 Name from NT_PropertyName where ID=PropertyNameId) as PropertyName";
        sql_GetData += " from NT_ClassProperty";
        sql_GetData += " where t.DataId=" + ID + "";

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, sql_GetData, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;

    }
    #endregion

    #endregion

    #region 数据相关

    #region 根据栏目名称取该栏目下属全部栏目的热门数据
    /// <summary>
    /// 根据上级栏目的名称取该栏目下属全部栏目的热门数据
    /// </summary>
    public static DataTable GetDataByCreateClassName(string Num, string CreateClassName, string DataType)
    {
        string Top = Num == "ALL" ? "" : " top " + Num;
        string strSql = "";
        strSql += " select " + Top + " [ID],[ClassId],[Title],[Intro],[Content],[TitlePicture],[AddTime] from NT_Data where ClassId in ";
        strSql += " (select t.[ID] from NT_Class t left join NT_Class a on t.CreateClassId=a.ID";
        strSql += " where a.ClassName='" + CreateClassName + "')";
        if (DataType == "固顶")
        {
            strSql += " and IsTop='是'";
        }
        if (DataType == "推荐")
        {
            strSql += " and IsRec='是'";
        }
        if (DataType == "热门")
        {
            strSql += " and IsHot='是'";
        }
        strSql += " order by id desc";
        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, strSql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];  
        }
        return dt;
    }
    #endregion


   
    #region 根据该栏目名称返回Data表的数据
    /// <summary>
    /// 根据该栏目名称返回Data表的数据
    /// </summary>
    public static DataTable GetDataByClassName(string Num, string ClassName, string DataType)
    {
        string Top = Num == "ALL" ? "" : " top " + Num;
        string sql_GetData = "select " +Top + " a.[ID] ,a.[ClassId],a.[Title],a.[Intro],a.[Content],a.[Traffic],a.[AddTime],b.[ClassName] as ClassId_Name  ";
        sql_GetData += " from NT_Data a ";
        sql_GetData += " left join NT_Class b on a.ClassId=b.Id";
        sql_GetData += " where b.ClassName='" + ClassName + "'";
        if (DataType == "固顶")
        {
            sql_GetData += " and a.IsTop='是'";
        }
        if (DataType == "推荐")
        {
            sql_GetData += " and a.IsRec='是'";
        }
        if (DataType == "热门")
        {
            sql_GetData += " and a.IsHot='是'";
        }
        sql_GetData += " order by a.Id desc";

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, sql_GetData, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;

    }
    #endregion

    #region 根据ID取数据的信息
    /// <summary>
    /// 根据ID取数据的信息
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public static DataRow GetDataByID(string ID)
    {
        //每次点击增加1点击量
        string sql_UpdateTraffic = " update NT_Data set [Traffic] = Traffic+1 where ID=" + ID + "";
        DbHelperOleDb.ExecuteNonQuery(CommandType.Text, sql_UpdateTraffic, null);

        string str_sql = "";
        str_sql += " select a.*,b.[ClassName] as ClassId_Name,b.[Id] as ClassId_Id  ";
        str_sql += " from NT_Data a ";
        str_sql += " left join NT_Class b on a.ClassId=b.Id";
        str_sql += " where 1=1";
        str_sql += " and a.ID=" + ID + "";

        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, str_sql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            return ds.Tables[0].Rows[0];
        }
        return null;
    }
    #endregion

    #region 根据ID来取Data附表的Property属性
    /// <summary>
    /// 根据ID来取Data附表的Property属性
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public static DataTable GetDataPropertyByID(string ID)
    {

        string sql_GetData = "";
        sql_GetData += " select a.[Title],";
        sql_GetData += " (select top 1 Name from NT_PropertyName where ID=t.PropertyNameId) as PropertyName,";
        sql_GetData += " t.[PropertyValue] ";
        sql_GetData += " from NT_DataProperty t";
        sql_GetData += " left join NT_Data a on t.DataId=a.ID";
        sql_GetData += " where t.DataId=" + ID + "";

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, sql_GetData, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;

    }
    #endregion

    #region 根据ID来取Data附表的Picture图片
    /// <summary>
    /// 根据ID来取Data附表的Picture图片
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public static DataTable GetDataPictureByID(string Num, string ID)
    {
        string Top = Num == "ALL" ? "" : " top " + Num;
        string sql_GetData = "";
        sql_GetData += " select "+Top+" [ID],[DataId],[PictureName],[PicturePath],[OrderNum] ";
        sql_GetData += " from NT_DataPicture";
        sql_GetData += " where DataId=" + ID + "";

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, sql_GetData, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;

    }
    #endregion

    #region 根据ID来取Data附表的File文件
    /// <summary>
    /// 根据ID来取Data附表的File文件
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public static DataTable GetDataDataFileByID(string ID)
    {

        string sql_GetData = "";
        sql_GetData += " select [ID],[DataId],[FileName],[FilePath],[OrderNum] ";
        sql_GetData += " from NT_DataFile";
        sql_GetData += " where DataId=" + ID + "";

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, sql_GetData, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;

    }
    #endregion
    #region 根据ID来取Data附表的Contents
    /// <summary>
    /// 根据ID来取Data附表的Contents
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public static DataTable GetDataContentsByID(string ID)
    {

        string sql_GetData = "";
        sql_GetData += " select [ID],[DataId],[ContentName],[ContentValue],[OrderNum] ";
        sql_GetData += " from NT_DataContent";
        sql_GetData += " where DataId=" + ID + "";

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, sql_GetData, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;

    }
    #endregion


    #endregion

    #region 获取友情链接

    public static DataTable GetLinks(string Num, string Type)
    {
        string Top = Num == "ALL" ? "" : " top " + Num;
        string str_sql = "select " + Top + "  [Id], [Type],[Title],[AltTitle],[PicturePath],[LinkPath],[OrderNum]";
        str_sql += " from NT_Links";
        if (Type != "ALL")
        {
            str_sql += " where Type='" + Type + "'";
        }
        str_sql += " order by OrderNum asc,Id desc";

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, str_sql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;
    }
    #endregion

    #region 根据MarkerValue取广告位
    /// <summary>
    /// 根据MarkerValue取广告位
    /// </summary>
    /// <param name="MarkerValue"></param>
    /// <returns></returns>
    public static DataRow GetAdvertisingByMarkerValue(string MarkerValue)
    {
        string str_sql = "select ";
        str_sql += " top 1 Id,Type,Title,FilePath,MarkerValue,OrderNum";
        str_sql += " from NT_Advertising";
        str_sql += " where MarkerValue='" + MarkerValue + "'";
        str_sql += " order by OrderNum asc,Id desc";

        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, str_sql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            return ds.Tables[0].Rows[0];
        }
        return null;
    }
    #endregion

    #region 根据MarkerValue取广告位
    /// <summary>
    /// 根据MarkerValue取广告位
    /// </summary>
    /// <param name="MarkerValue"></param>
    /// <returns></returns>
    public static DataTable GetAdvertising()
    {
        string str_sql = "select ";
        str_sql += "  * ";
        str_sql += " from NT_Advertising";
        str_sql += " order by OrderNum asc,Id desc";

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, str_sql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            return ds.Tables[0];
        }
        return dt;
    }
    #endregion


    #region 根据栏目ID取该栏目的属性配置
    /// <summary>
    /// 根据栏目ID取该栏目的属性配置
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public static DataTable GetClassPropertyByClassID(string ID)
    {
        string sql_GetData = "select DISTINCT pn.[ID],pn.[Name] ";
        sql_GetData += "from (NT_Class c inner join NT_ClassProperty cp on c.ID=cp.ClassId) ";
        sql_GetData += "inner join NT_PropertyName pn on pn.ID=cp.PropertyNameId ";
        sql_GetData += "where c.ID in("+ID+")";
        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, sql_GetData, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;

    }
    #endregion

    #region 根据属性名称ID取第一个与之匹配的栏目ID
    /// <summary>
    /// 根据属性名称ID取第一个与之匹配的栏目ID
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public static string GetClassIDByPropertyNameId(string PropertyNameId)
    {
        string temp = "";
        string strSql = "SELECT top 1 ClassId FROM NT_ClassProperty where PropertyNameId=" + PropertyNameId;
        object obj = DbHelperOleDb.ExecuteScalar(CommandType.Text, strSql, null);
        if (obj != null)
        {
            temp = obj.ToString();
        }
        return temp;

    }
    #endregion

    #region 根据栏目ID取的栏目信息
    /// <summary>
    /// 根据属性名称ID取第一个与之匹配的栏目ID
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public static DataTable GetClassInfoById(string id)
    {
        string strSql = "SELECT * from  NT_Class  where id in (" + id+")";
        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, strSql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            return dt = ds.Tables[0];
        }
        else {
            return null;
        }

    }
    #endregion
    #region 根据属性Id取属性值
    /// <summary>
    /// 根据属性Id取属性值
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public static DataTable GetPropertyValuesByPropertyId(string ID)
    {
        string sql_GetData = "select [ID],[Name] ";
        sql_GetData += "from NT_PropertyValue where PropertyNameId=" + ID;
        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, sql_GetData, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;

    }
    #endregion


    #region  根据该栏目ID返回Data表的数据
    /// <summary>
    /// 根据该栏目ID返回Data表的数据
    /// </summary>
    /// <param name="Num"></param>
    /// <param name="ClassID"></param>
    /// <param name="DataType"></param>
    /// <returns></returns>
    public static DataTable GetDataByClassID(string Num, string ClassID, string DataType)
    {
        string Top = Num == "ALL" ? "" : " top " + Num;
        string sql_GetData = "select " + Top + " a.[ID],a.[IsTop] ,a.[ClassId],a.[Title],a.[TitlePicture],a.[Intro],a.[Content],a.[FilePath],a.[Video1],a.[Video2],a.[AddTime],b.[ClassName] as ClassId_Name  ";
        sql_GetData += " from NT_Data a ";
        sql_GetData += " left join NT_Class b on a.ClassId=b.Id ";
        //sql_GetData += " where b.ID=" + ClassID;
        sql_GetData += " where b.ID in(" + ClassID+")";
        if (DataType == "固顶")
        {
            sql_GetData += " and a.IsTop='是'";
        }
        if (DataType == "推荐")
        {
            sql_GetData += " and a.IsRec='是'";
        }
        if (DataType == "热门")
        {
            sql_GetData += " and a.IsHot='是'";
        }
        sql_GetData += " order by a.AddTime desc,a.ID desc";

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, sql_GetData, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;

    }
    #endregion

    #region   根据数据ID获取该数据的所有属性值
    /// <summary>
    /// 根据数据ID获取该数据的所有属性值
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public static DataTable GetPropertyValueByDataID(string ID)
    {
        string sql_GetData = "select pv.[Name],pn.[Name] as propertyname ";
        sql_GetData += "from ( NT_PropertyValue pv inner join NT_DataProperty dp on pv.ID=dp.PropertyValue ) ";
        sql_GetData += "inner join NT_PropertyName pn on pn.ID=dp.PropertyNameId ";
        sql_GetData += "where dp.DataId="+ID;

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, sql_GetData, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0];
        }
        return dt;

    }
    #endregion

    #region 根据点击量的次数来排序该栏目返回Data表的数据
    /// <summary>
    /// 根据点击量的次数来排序该栏目返回Data表的数据
    /// </summary>
    /// <param name="Num">显示几条数据</param>
    /// <param name="CreateClassName">栏目名称</param>
    /// <returns></returns>
    public static DataTable GetDataByClassNameAndTraffic(string Num, string CreateClassName)
    {
        string Top = Num == "ALL" ? "" : " top " + Num;
        string strSql = "select top "+Num+" [ID],[Title],[Intro],[Content],[Traffic],[AddTime] from NT_Data where id in(select top "+Num+" [ID] from NT_Data where ClassId in(select t.[ID] from NT_Class t left join NT_Class a on t.CreateClassId=a.ID where a.ClassName='"+CreateClassName+"')order by Traffic desc)";
        //string strSql = "";
        //strSql += " select " + Top + " [ID],[Title],[Intro],[Content],[Traffic],[AddTime] from NT_Data where ClassId in ";
        //strSql += " (select t.[ID] from NT_Class t left join NT_Class a on t.CreateClassId=a.ID";
        //strSql += " where a.ClassName='" + CreateClassName + "')";
        //strSql += " order by Traffic desc";

        DataTable dt = new DataTable();
        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, strSql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            dt = ds.Tables[0]; 
        }
        return dt;
    }
    #endregion

    public static string Cutstring(string Content,int length)
    { 
      
        return   Content.Length >length ?Content.Substring (0,length)+"...":Content;


    }


    #region 下一页
    public static DataTable GetNextID(string ID)
    {
        string str_sql = "";
        str_sql += " select top 1 * from NT_Data ";
        str_sql += " where ID>" + ID + " and ClassId in(select ClassId from NT_Data where ID=" + ID + ") order by ID desc";

        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, str_sql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            return ds.Tables[0];
        }
        return null;
    }
    #endregion
    #region 上一页
    public static DataTable GetPreID(string ID)
    {
        string str_sql = "";
        str_sql += " select top 1 * from NT_Data ";
        str_sql += " where ID<" + ID + "and ClassId in(select ClassId from NT_Data where ID=" + ID + ") order by ID desc";

        DataSet ds = DbHelperOleDb.ExecuteDataSet(CommandType.Text, str_sql, null);
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {
            return ds.Tables[0];
        }
        return null;
    }
    #endregion

}
