using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using SqlOperate;

/// <summary>
/// R_Consultative 的摘要说明
/// </summary>
namespace Consultative
{
    public class RConsultative : IConsultative
    {
        public static string ConnectionString = ConfigurationManager.ConnectionStrings["sqlData"].ConnectionString;

        #region IConsultative 成员
        /// <summary>
        /// 添加资讯信息
        /// </summary>
        /// <param name="con">咨询信息对象</param>
        /// <returns></returns>
        public int Insert(TConsultative con)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {                
                  
					new SqlParameter("@Title", con.Title),
                    new SqlParameter("@Color", con.Color),
					new SqlParameter("@Font", con.Font),
					new SqlParameter("@TypeId", con.TypeID),
					new SqlParameter("@Picture", con.Picture),
					new SqlParameter("@Origin", con.Origin),
                    new SqlParameter("@KeyWord",con.KeyWord),
                    new SqlParameter("@Author",con.Author),
                    new SqlParameter("@Reading",con.Reading),
					new SqlParameter("@ChangeDirectionLink", con.ChangeDirectionLink),
					new SqlParameter("@Detail", con.Detail),
                    new SqlParameter("@AddDate",con.AddDate),
                    new SqlParameter("@ClickCount",con.ClickCount),
                    new SqlParameter("@CountUp",con.CountUp),
                    new SqlParameter("@CountDown",con.CountDown),
                    new SqlParameter("@IsTop",con.IsTop),
                    new SqlParameter("@IsRecommend",con.IsRecommend),
                    new SqlParameter("@IsComment",con.IsComment),
                    new SqlParameter("@IsHotspot",con.IsHotspot)

             };
            return SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "SP_W_Consultative_Add", commandParameters);
        }
        /// <summary>
        /// 修改资讯信息 
        /// </summary>
        /// <param name="con">资讯信息对象</param>
        /// <returns></returns>
        public int Update(TConsultative con)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {
              		new SqlParameter("@ID",con.ID),
                    new SqlParameter("@Title", con.Title),
                    new SqlParameter("@Color", con.Color),
					new SqlParameter("@Font", con.Font),
					new SqlParameter("@TypeId", con.TypeID),
					new SqlParameter("@Picture", con.Picture),
					new SqlParameter("@Origin", con.Origin),
                    new SqlParameter("@KeyWord",con.KeyWord),
                    new SqlParameter("@Author",con.Author),
                    new SqlParameter("@Reading",con.Reading),
					new SqlParameter("@ChangeDirectionLink", con.ChangeDirectionLink),
					new SqlParameter("@Detail", con.Detail),
                    new SqlParameter("@AddDate",con.AddDate),
                    new SqlParameter("@ClickCount",con.ClickCount),
                    new SqlParameter("@IsTop",con.IsTop),
                    new SqlParameter("@IsRecommend",con.IsRecommend),
                    new SqlParameter("@IsComment",con.IsComment),
                    new SqlParameter("@IsHotspot",con.IsHotspot)


            };
            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "SP_W_Consultative_Update", commandParameters);
        }
        /// <summary>
        /// 根据ID删除资讯信息
        /// </summary>
        /// <param name="ID">资讯ID</param>
        /// <returns></returns>
        public int Delete(int ID)
        {
            SqlParameter[] param = new SqlParameter[] 
            {
                new SqlParameter("@strwhere", "where ID in (" + ID + ")"), 
                new SqlParameter("@strTableName", "W_Consultative") 
            };
            return SqlHelper.DeleteByWhere("SP_DeleteByWhere", param);

        }
        /// <summary>
        /// 根据资讯类别TypeID删除资讯信息
        /// </summary>
        /// <param name="ID">资讯类别TypeID</param>
        /// <returns></returns>
        public int DeleteByTypeID(string TypeID)
        {
            SqlParameter[] param = new SqlParameter[] 
            {
                new SqlParameter("@strwhere", "where TypeID in (" + TypeID + ")"), 
                new SqlParameter("@strTableName", "W_Consultative") 
            };
            return SqlHelper.DeleteByWhere("SP_DeleteByWhere", param);

        }
        /// <summary>
        /// 取得所有资讯信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            SqlParameter[] param = new SqlParameter[] 
            { 
                new  SqlParameter("@strSelect","a.ID,a.Title,a.TypeID,a.Picture,a.KeyWord,a.Author,a.Origin,a.Reading,a.AddDate,a.ClickCount,a.CountUp,a.CountDown,a.IsTop,a.IsRecommend,a.Hotspot,a.Comment,b.Name"),
                new SqlParameter("@strwhere", ""),
                new SqlParameter("@strtableName", "W_Consultative as a LEFT  JOIN W_ConsultativeType as b ON a.TypeID = b.ID"),
                new SqlParameter("@strOrder", "  order by a.AddDate desc") 
            };
            return SqlHelper.ExecuteTable(CommandType.StoredProcedure, "SP_SelectByWhere", param);
        }
        /// <summary>
        /// 根据标题和资讯类别得到资讯信息
        /// </summary>
        /// <param name="Parameter">资讯信息对象</param>
        /// <returns></returns>
        public DataTable GetDataTable(TConsultative con)
        {
            string str = "where 1=1";
            if (con.Title != null)
            {
                if (con.Title != "")
                {
                    str = str + " and Title like '%" + con.Title + "%' ";
                }
            }
            if (con.TypeID != 0)
            {
                str = str + " and TypeID =" + con.TypeID;
            }


            SqlParameter[] param = new SqlParameter[] 
            {
                new  SqlParameter("@strSelect","a.ID,a.Title,a.TypeID,a.Picture,a.Attribute,a.KeyWord,a.Author,a.Origin,a.Reading,a.AddDate,a.ClickCount,a.ClickCount,a.CountUp,a.CountDown,a.IsTop,a.IsRecommend,a.Hotspot,a.Comment,b.Name"),
                new SqlParameter("@strwhere", str),
                new SqlParameter("@strtableName", "W_Consultative as a LEFT  JOIN W_ConsultativeType as b ON a.TypeID = b.ID"),
                new SqlParameter("@strOrder", "  order by a.AddDate desc") 
            };
            return SqlHelper.ExecuteTable(CommandType.StoredProcedure, "SP_SelectByWhere", param);
        }

        /// <summary>
        /// 根据ID得到资讯每一条信息
        /// </summary>
        /// <param name="id">资讯ID</param>
        /// <returns>返回资讯信息的对象</returns>
        public TConsultative GateItem(int id)
        {
            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere", "where ID=" +Convert.ToInt32( id)), 
                new SqlParameter("@strTableName", "W_Consultative"), 
                new SqlParameter("@strOrder", " order by AddDate desc,ID desc") 
            };
            TConsultative info = new TConsultative();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "SP_SelectByWhere", param))
            {
                if (!reader.Read())
                {
                    return info;
                }
                if (reader["ID"].ToString() != "")
                {
                    info.ID = Convert.ToInt32(reader["ID"].ToString());
                }
                info.Title = reader["TItle"].ToString();
                info.Color = reader["Color"].ToString();
                info.Font = reader["Font"].ToString();
                if (reader["TypeID"].ToString() != "")
                {
                    info.TypeID = Convert.ToInt32(reader["TypeID"].ToString());
                }
                info.Picture = reader["Picture"].ToString();
                info.KeyWord = reader["KeyWord"].ToString();
                info.Author = reader["Author"].ToString();
                if (reader["AddDate"].ToString() != "")
                {
                    info.AddDate = Convert.ToDateTime(reader["AddDate"].ToString());
                }
                info.Reading = reader["Reading"].ToString();
                info.ClickCount = reader["ClickCount"].ToString();
                info.Origin = reader["Origin"].ToString();
                info.ChangeDirectionLink = reader["ChangeDirectionLink"].ToString();
                info.Detail = reader["Detail"].ToString();
                info.CountUp = int.Parse(reader["CountUp"].ToString());
                info.CountDown = int.Parse(reader["CountDown"].ToString());
                info.IsHotspot = Convert.ToBoolean(reader["IsHotspot"].ToString());
                info.IsComment = Convert.ToBoolean(reader["IsComment"].ToString());
                info.IsRecommend = Convert.ToBoolean(reader["IsRecommend"].ToString());
                info.IsTop =Convert.ToBoolean(reader["IsTop"].ToString());

            }
            return info;
        }
        /// <summary>
        /// 修改资讯信息 
        /// </summary>
        /// <param name="con">资讯信息对象</param>
        /// <returns></returns>
        public void UpdateCount(int id, int temp)
        { 
            string strTableName="[W_Consultative]";
            string strDate="";
            string strWhere="ID="+id;
            if ( temp == 1 )
            {
                strDate="[CountUp]=[CountUp]+1";
            }
            else 
            {
                strDate="[CountDown]=[CountDown]+1";
            }
            SqlParameter[] commandParameters = new SqlParameter[]
            {
                new SqlParameter("@strTableName",strTableName),
                new SqlParameter("@strDate",strDate),
                new SqlParameter("@strWhere",strWhere)
                
            };
            int i=SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "[SP_P_UpdateByWhere]", commandParameters);
        }


        #endregion ICorporation 成员



        /// <summary>
        ///  标签调用查询资讯列表
        /// </summary>
        /// <param name="strSelect"></param>
        /// <param name="strWhere"></param>
        /// <param name="strTableName"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string strSelect, string strWhere, string strTableName)
        {
            SqlParameter[] param = new SqlParameter[] 
            {  
                new SqlParameter("@StrSelect",strSelect),
                new SqlParameter("@strWhere",strWhere),
                new SqlParameter("@strTableName", strTableName), 
                new SqlParameter("@strOrder", "")
            };
            return SqlHelper.ExecuteTable(CommandType.StoredProcedure, "SP_SelectByWhere", param);
        }


    }
}
