?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 System.Data.SqlClient;
using System.IO ;
using System.Collections.Generic;
using System.Drawing;
using System.Drawing.Drawing2D;
/// <summary>
/// BookManager 圖書及圖書類別業務錠邏輯層
/// </summary>
public class BookManager
{
    public BookManager()
    {


    }
   //獲得類別列表
    public static List<BookCategory> GetCategory()
    
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BookShow"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetCategory", connection))
           {
               command.CommandType = CommandType.StoredProcedure;
               connection.Open();
               List<BookCategory> list = new List<BookCategory>();
               using (SqlDataReader reader = command.ExecuteReader())
               {
                   while (reader.Read())
                   {
                       BookCategory temp = new BookCategory((int)reader["CategoryID"], (string)reader["CategoryName"], (string)reader["CategoryRemark"], (int)reader["NumbersofBooks"]);
                       list.Add(temp);
                   }
               }

               return list;
            }
        }
    }
    //獲得圖書列表通過類別名和用戶名  ，注：管理自己圖書時調用
    public static List<Book> GetBookbyCategoryAndName(int CategoryID)
    {
        MembershipUser user = Membership.GetUser();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BookShow"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetBookbyCategoryAndName", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@CategoryID", CategoryID));
                command.Parameters.Add(new SqlParameter("@UserID", user.UserName.ToString()));
                List<Book> list = new List<Book>();                                 
                connection.Open();
                using (SqlDataReader reader = command .ExecuteReader ())
                {
                    while (reader.Read())
                    { Book temp = new Book((int)reader["BookID"], (string)reader["BookName"], (int)reader["CategoryID"], (string)reader["ISBN"], (string)reader["UserID"], (string)reader["BookTips"], (DateTime)reader["AddDateTime"],-1);
                    list.Add(temp);
                    }
                }
                return list;
            }
        }
    }
    //獲得圖書列表通過類別名 ,注：呈現圖書時調用
    public static List<Book> GetBookbyCategory(int CategoryID)
    {
      //  MembershipUser user = Membership.GetUser();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BookShow"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetBookbyCategory", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("CategoryID", CategoryID));
                 List<Book> list = new List<Book>();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Book temp = new Book((int)reader["BookID"], (string)reader["BookName"], (int)reader["CategoryID"], (string)reader["ISBN"], (string)reader["UserID"], (string)reader["BookTips"], (DateTime)reader["AddDateTime"],-1);
                        list.Add(temp);
                    }
                }
                return list;
            }
        }
    }
    //產生首頁隨機圖書的方法。
    public static List<Book> GetBooks()
    {
       return  GetBookbyCategory(GetRandomCategory());
    }
    //獲得一個隨機非空類別ID
    public static int GetRandomCategory()
    {
        using(SqlConnection  connection = new SqlConnection (ConfigurationManager .ConnectionStrings["BookShow"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand ("GetNotEmptyCategory",connection ))
            {
                command.CommandType=CommandType.StoredProcedure;
                connection .Open ();
                List<BookCategory> list= new List<BookCategory> ();
                SqlDataReader reader = command .ExecuteReader();
                while(reader .Read ())
                {
                    BookCategory temp = new BookCategory (Convert.ToInt32(reader["CategoryID"]),"","",0);
                    list.Add(temp);
                }
                try
                { Random ran = new Random() ;
                return list[ran.Next(list.Count)].CategoryID;
                }
                catch
                {
                    return -1;
                }
            }
        }
    }
//通過BookID獲得圖書信息
    public static Book GetBookbyBookID(int BookID)
    {
        Book book = null;

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BookShow"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetBookbyBookID", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("BookID", BookID));
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        book  = new Book((int)reader["BookID"], (string)reader["BookName"], (int)reader["CategoryID"], (string)reader["ISBN"], (string)reader["UserID"], (string)reader["BookTips"], (DateTime)reader["AddDateTime"],-1);
                       
                    }
                }
                return book;
            }
        }
    }
   
    //獲得最新加入的圖書
public static List<Book> Get5newBooks()
    {
      //  MembershipUser user = Membership.GetUser();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BookShow"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("select top 4 BookID,BookName,CategoryID,ISBN,UserID,BookTips,AddDateTime from Book order by AddDateTime desc", connection))
            {
                command.CommandType = CommandType.Text;
                List<Book> list = new List<Book>();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Book temp = new Book((int)reader["BookID"], (string)reader["BookName"], (int)reader["CategoryID"], (string)reader["ISBN"], (string)reader["UserID"], (string)reader["BookTips"], (DateTime)reader["AddDateTime"],-1);
                        list.Add(temp);
                    }
                }
                return list;
            }
        }
    }
    //獲得最多評論的前4本書
    public static List<Book> GetHotBooks()
    {
        //  MembershipUser user = Membership.GetUser();
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BookShow"].ConnectionString))
        {
            using (SqlCommand command = new SqlCommand("GetHotBooks", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                List<Book> list = new List<Book>();
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Book temp = new Book((int)reader["BookID"], (string)reader["BookName"],0,"","","",DateTime .Now ,Convert.ToInt32 (reader ["hot"]));
                        list.Add(temp);
                    }
                }
                return list;
            }
        }
    }
    //
    //public static Book  GetCategorybyBookID(int BookID)
    //{
    //    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["BookShow"].ConnectionString))
    //    {
    //        using (SqlCommand command = new SqlCommand("GetBookbyBookID", connection))
    //        {
    //            command.CommandType = CommandType.StoredProcedure;
    //            command.Parameters.Add(new SqlParameter("BookID", BookID));
    //            Book book = null;
    //            connection.Open();
    //            using (SqlDataReader reader = command.ExecuteReader())
    //            {
    //                while (reader.Read())
    //                {
    //                    book = new Book((int)reader["BookID"], (string)reader["BookName"], (int)reader["CategoryID"], (string)reader["ISBN"], (string)reader["UserID"], (string)reader["BookTips"], (DateTime)reader["AddDateTime"],-1);