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 Web25175.DBHelper;
using System.Data.SqlClient;

/// <summary>
/// N38_Book 的摘要说明
/// </summary>
public class N38_Book
{
	public N38_Book()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}

    /// <summary>
    /// 添加图书
    /// </summary>
    /// <param name="Name"></param>
    /// <param name="Catalog"></param>
    /// <param name="Price"></param>
    /// <param name="Amount"></param>
    /// <param name="ISBN"></param>
    /// <param name="State"></param>
    /// <returns></returns>
    public bool AddNewBook(string Name, string Catalog, float Price, int Amount, string ISBN, string State)
    {
        if (this.ExitBook(Name))
        {
            string sqlStr = "update n38_25175_book set n38_book_amount=n38_book_amount+" + Amount + " where n38_book_name='" + Name + "'";
            int num = DBManager.Instance().ExecuteNonQuery(CommandType.Text, sqlStr);
            if (num > 0)
                return true;
            else
                return false;
        }
        else
        {
            string sqlStr = "insert into n38_25175_book (n38_book_name,n38_book_catalog,n38_book_price,n38_book_amount,n38_book_isbn,n38_book_state) values('" + Name + "','" + Catalog + "'," + Price + "," + Amount + ",'" + ISBN + "','" + State + "')";
            int num = DBManager.Instance().ExecuteNonQuery(CommandType.Text, sqlStr);
            if (num > 0)
                return true;
            else
                return false;
        }
    }

    /// <summary>
    /// 检查书名是否存在
    /// </summary>
    /// <param name="BookName"></param>
    /// <returns></returns>
    public bool ExitBook(string BookName)
    {
        string sqlStr = "select * from n38_25175_book where n38_book_name='" + BookName + "'";
        bool result=DBManager.Instance().CheckData(sqlStr);
        if(result)
            return true ;
        else 
            return false ;
    }
    /// <summary>
    /// 根据id检查图书是否存在
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public bool ExitBookByID(int ID)
    {
        string sqlStr = "select * from n38_25175_book where n38_book_id='" + ID  + "'";
        bool result = DBManager.Instance().CheckData(sqlStr);
        if (result)
            return true;
        else
            return false;
    }
    /// <summary>
    /// 删除图书
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public bool DeleteBook(int ID,string BookName)
    {
        if (this.BookBorrow(BookName))
        {
            return false;
        }
        else
        {
            if (this.BookLost(BookName))
            {
                return false;
            }
            else
            {
                string sqlStr = "delete from n38_25175_book where n38_book_id=" + ID + "";
                int num = DBManager.Instance().ExecuteNonQuery(CommandType.Text, sqlStr);
                if (num > 0)
                    return true;
                else
                    return false;
            }
        }
    }

    /// <summary>
    /// 根据id获取书名
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public string GetBookName(int ID)
    {
        string sqlStr = "select *from n38_25175_book where n38_book_id=" + ID + "";
        SqlDataReader myRead = DBManager.Instance().ExecuteReader(sqlStr);
        string name = "";
        while (myRead.Read())
        {
            name = myRead["n38_book_name"].ToString().Trim();
        }
        myRead.Close();
        return name;
    }

    
    /// <summary>
    /// 根据id获取分类名
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public string GetBookCatalog(int ID)
    {
        string sqlStr = "select *from n38_25175_book where n38_book_id=" + ID + "";
        SqlDataReader myRead = DBManager.Instance().ExecuteReader(sqlStr);
        string name = "";
        while (myRead.Read())
        {
            name = myRead["n38_book_catalog"].ToString();
        }
        myRead.Close();
        return name;
    }
    /// <summary>
    /// 检查图书是否被借出
    /// </summary>
    /// <param name="BookName"></param>
    /// <returns></returns>
    public bool BookBorrow(string BookName)
    {
        string sqlStr = "select * from n38_25175_borrow where n38_borrow_bookname='" + BookName + "'";
        bool result = DBManager.Instance().CheckData(sqlStr);
        if (result)
            return true;
        else
            return false; 
    }

    /// <summary>
    /// 检查图书是否挂失
    /// </summary>
    /// <param name="BookName"></param>
    /// <returns></returns>
    public bool BookLost(string BookName)
    {
        string sqlStr = "select * from n38_25175_lost where n38_lost_bookname='" + BookName + "'";
        bool result = DBManager.Instance().CheckData(sqlStr);
        if (result)
            return true;
        else
            return false;
    }

    /// <summary>
    /// 修改图书信息
    /// </summary>
    /// <param name="Name"></param>
    /// <param name="Catalog"></param>
    /// <param name="Price"></param>
    /// <param name="Amount"></param>
    /// <param name="ISBN"></param>
    /// <param name="State"></param>
    /// <returns></returns>
    public bool UpdateBook(int ID,string Name, string Catalog, float Price, int Amount, string ISBN)
    {
        
            string sqlStr = "update n38_25175_book set n38_book_name='" + Name + "',n38_book_catalog='" + Catalog + "',n38_book_price=" + Price + ",n38_book_amount=" + Amount + ",n38_book_isbn='" + ISBN + "' where n38_book_id=" + ID + "";
            int num = DBManager.Instance().ExecuteNonQuery(CommandType.Text, sqlStr);
            if (num > 0)
                return true;
            else
                return false;
        
    }

    /// <summary>
    /// 读取图书信息到文本框
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="Name"></param>
    /// <param name="Catalog"></param>
    /// <param name="Price"></param>
    /// <param name="Amount"></param>
    /// <param name="ISBN"></param>
    public void GetBookData(int ID, TextBox Name, DropDownList Catalog, TextBox Price, TextBox Amount, TextBox ISBN)
    {
        string sqlStr = "select *from n38_25175_book where n38_book_id=" + ID + "";
        SqlDataReader myRead = DBManager.Instance().ExecuteReader(sqlStr);
        while (myRead.Read())
        {
            Name.Text = myRead["n38_book_name"].ToString();
            Catalog.SelectedValue = myRead["n38_book_catalog"].ToString();
            Price.Text = myRead["n38_book_price"].ToString();
            Amount.Text = myRead["n38_book_amount"].ToString();
            ISBN.Text = myRead["n38_book_ISBN"].ToString();
        }
        myRead.Close();
    }

    /// <summary>
    /// 查询图书，数据绑定DataSet
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="BookName"></param>
    /// <param name="Catalog"></param>
    /// <param name="Price"></param>
    /// <param name="ISBN"></param>
    /// <returns></returns>
    public DataSet SelectBook(TextBox ID, TextBox BookName, DropDownList Catalog, TextBox Price, TextBox ISBN)
    {
        DataSet ds = new DataSet();
        string catalog = Catalog.SelectedValue;
        string sb = "select * from n38_25175_book where n38_book_catalog='" + catalog + "'";
        if (ID.Text != "")
        {
            int id = int.Parse(ID.Text.Trim());
            sb = sb + "and n38_book_id=" + id + "";
        }
        if (BookName.Text != "")
        {
            sb = sb + "and n38_book_name like '%" + BookName.Text.Trim() + "%'";
        }
        if (Price.Text != "")
        {
            float price = float.Parse(Price.Text.Trim());
            sb = sb + "and n38_book_price=" + price + "";
        }
        if (ISBN.Text != "")
        {
            sb = sb + "and n38_book_ISBN='" + ISBN.Text.Trim() + "'";
        }
        ds = DBManager.Instance().ExecuteDataSet(CommandType.Text, sb);
        return ds;
    }

    /// <summary>
    /// 条件查询借书
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="BookName"></param>
    /// <param name="Name"></param>
    /// <returns></returns>
    public DataSet SelectBorrowData(TextBox BookName, TextBox Name,int pageindex,int pagesize)
    {
        DataSet ds = new DataSet();
        string sb = "select n38_25175_borrow.n38_borrow_id,n38_25175_borrow.n38_borrow_bookname,n38_25175_borrow.n38_borrow_date,n38_25175_borrow.n38_borrow_state,n38_25175_user.n38_user_realname,n38_25175_user.n38_user_type from n38_25175_borrow join n38_25175_user on n38_25175_borrow.n38_borrow_username=n38_25175_user.n38_user_name ";
        if (BookName.Text != "")
        {
            if (Name.Text == "")
            {
                sb = sb + " where n38_borrow_bookname like '%" + BookName.Text.Trim() + "%'";
            }
            else
            {
                string name = Name.Text.Trim();
                N38_User myUser = new N38_User();
                string username = myUser.GetName(name);
                sb = sb + " where n38_borrow_bookname like '%" + BookName.Text.Trim() + "%' and n38_borrow_username='" + username + "'";
            }
        }
        else
        {
            if (Name.Text != "")
            {
                string name = Name.Text.Trim();
                N38_User myUser = new N38_User();
                string username = myUser.GetName(name);
                sb = sb + " where n38_borrow_username='" + username + "'";
            }
        }
        ds = DBManager.Instance().ExecuteDataSet2(CommandType.Text, sb, pageindex, pagesize, "n38_25175_borrow");
        return ds;
    }
    /// <summary>
    /// 条件查询借书条数
    /// </summary>
    /// <param name="BookName"></param>
    /// <param name="Name"></param>
    /// <returns></returns>
    public int SelectBorrowCount(TextBox BookName, TextBox Name)
    {
        string sb = "select count(*) from n38_25175_borrow";
        if (BookName.Text != "")
        {
            if (Name.Text == "")
            {
                sb = sb + " where n38_borrow_bookname like '%" + BookName.Text.Trim() + "%'";
            }
            else
            {
                string name = Name.Text.Trim();
                N38_User myUser = new N38_User();
                string username = myUser.GetName(name);
                sb = sb + " where n38_borrow_bookname like '%" + BookName.Text.Trim() + "%' and n38_borrow_username='" + username + "'";
            }
        }
        else
        {
            if (Name.Text != "")
            {
                string name = Name.Text.Trim();
                N38_User myUser = new N38_User();
                string username = myUser.GetName(name);
                sb = sb + " where n38_borrow_username='" + username + "'";
            }
        }
        int num = int.Parse(DBManager.Instance().ExecuteScalar(CommandType.Text, sb).ToString());
        return num;
    }

    /// <summary>
    /// 条件查询挂失条数
    /// </summary>
    /// <param name="BookName"></param>
    /// <param name="Name"></param>
    /// <returns></returns>
    public int SelectLostCount(TextBox BookName, TextBox Name)
    {
        string sb = "select count(*) from n38_25175_lost";
        if (BookName.Text != "")
        {
            if (Name.Text == "")
            {
                sb = sb + " where n38_lost_bookname like '%" + BookName.Text.Trim() + "%'";
            }
            else
            {
                string name = Name.Text.Trim();
                N38_User myUser = new N38_User();
                string username = myUser.GetName(name);
                sb = sb + " where n38_lost_bookname like '%" + BookName.Text.Trim() + "%' and n38_lost_username='" + username + "'";
            }
        }
        else
        {
            if (Name.Text != "")
            {
                string name = Name.Text.Trim();
                N38_User myUser = new N38_User();
                string username = myUser.GetName(name);
                sb = sb + " where n38_lost_username='" + username + "'";
            }
        }
        int num = int.Parse(DBManager.Instance().ExecuteScalar(CommandType.Text, sb).ToString());
        return num;
    }

    /// <summary>
    /// 条件查询挂失数据集
    /// </summary>
    /// <param name="BookName"></param>
    /// <param name="Name"></param>
    /// <param name="pageindex"></param>
    /// <param name="pagesize"></param>
    /// <returns></returns>
    public DataSet SelectLostData(TextBox BookName, TextBox Name, int pageindex, int pagesize)
    {
        DataSet ds = new DataSet();
        string sb = "select n38_25175_lost.n38_lost_id,n38_25175_lost.n38_lost_bookname,n38_25175_lost.n38_lost_date,n38_25175_user.n38_user_realname from n38_25175_lost join n38_25175_user on n38_25175_lost.n38_lost_username=n38_25175_user.n38_user_name";
        if (BookName.Text != "")
        {
            if (Name.Text == "")
            {
                sb = sb + " where n38_lost_bookname like '%" + BookName.Text.Trim() + "%'";
            }
            else
            {
                string name = Name.Text.Trim();
                N38_User myUser = new N38_User();
                string username = myUser.GetName(name);
                sb = sb + " where n38_lost_bookname like '%" + BookName.Text.Trim() + "%' and n38_lost_username='" + username + "'";
            }
        }
        else
        {
            if (Name.Text != "")
            {
                string name = Name.Text.Trim();
                N38_User myUser = new N38_User();
                string username = myUser.GetName(name);
                sb = sb + " where n38_lost_username='" + username + "'";
            }
        }
        ds = DBManager.Instance().ExecuteDataSet2(CommandType.Text, sb, pageindex, pagesize, "n38_25175_lost");
        return ds;
    }
    /// <summary>
    /// 更新指定用户的借书状态
    /// </summary>
    /// <param name="MaxDay"></param>
    /// <param name="UserName"></param>
    /// <returns></returns>
    public bool OvertimeBorrowBook(int MaxDay,string UserName,string BookName,DateTime BorrowTime)
    {
        string sqlStr = "update n38_25175_borrow set n38_borrow_state='超期' where datediff(day,n38_borrow_date,getdate())>" + MaxDay + " and n38_borrow_username='" + UserName + "'";
        string sqlStr2 = "insert into n38_25175_overtime (n38_overtime_bookname,n38_overtime_username,n38_overtime_borrowtime) values('" + BookName + "','" + UserName + "','" + BorrowTime + "')";
        try
        {
            DBManager.Instance().BeginTrans();
            DBManager.Instance().ExecuteNonQuery(CommandType.Text, sqlStr);
            DBManager.Instance().ExecuteNonQuery(CommandType.Text, sqlStr2);
            DBManager.Instance().CommitTrans();
            return true;
        }
        catch (Exception)
        {
            DBManager.Instance().RollbackTrans();
            return false;
        }
    }
    /// <summary>
    /// 更新全部用户借书状态
    /// </summary>
    /// <param name="MaxDay"></param>
    /// <param name="UserName"></param>
    /// <param name="BookName"></param>
    /// <param name="BorrowTime"></param>
    /// <returns></returns>
    public bool OvertimeAllBorrow(int MaxDay, string UserName, string BookName, DateTime BorrowTime)
    {
        string sqlStr = "update n38_25175_borrow set n38_borrow_state='超期' where datediff(day,n38_borrow_date,getdate())>" + MaxDay + " and n38_borrow_username='" + UserName + "'";
        string sqlStr2 = "insert into n38_25175_overtime (n38_overtime_bookname,n38_overtime_username,n38_overtime_borrowtime) values('" + BookName + "','" + UserName + "','" + BorrowTime + "')";
        try
        {
            DBManager.Instance().BeginTrans();
            DBManager.Instance().ExecuteNonQuery(CommandType.Text, sqlStr);
            DBManager.Instance().ExecuteNonQuery(CommandType.Text, sqlStr2);
            DBManager.Instance().CommitTrans();
            return true;
        }
        catch (Exception)
        {
            DBManager.Instance().RollbackTrans();
            return false;
        }
    }
    /// <summary>
    /// 把指定图书借阅状态改成正常
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public bool NormalBorrowBook(string bookname,string UserName)
    {
        string sqlStr = "update n38_25175_borrow set n38_borrow_state='正常' where n38_borrow_bookname='" + bookname + "' and n38_borrow_username='" + UserName + "'";
        string sqlStr2 = "delete from n38_25175_overtime where n38_overtime_bookname='" + bookname + "' and n38_overtime_username='" + UserName + "'";
        try
        {
            DBManager.Instance().BeginTrans();
            DBManager.Instance().ExecuteNonQuery(CommandType.Text, sqlStr);
            DBManager.Instance().ExecuteNonQuery(CommandType.Text, sqlStr2);
            DBManager.Instance().CommitTrans();
            return true;
        }
        catch (Exception)
        {
            DBManager.Instance().RollbackTrans();
            return false;
        }
    }

    /// <summary>
    /// 根据书名和用户名获取借书时间
    /// </summary>
    /// <param name="BookName"></param>
    /// <param name="UserName"></param>
    /// <returns></returns>
    public DateTime GetBorrowDate(string BookName,string UserName)
    {
        string sqlStr = "select convert(varchar(10),n38_borrow_date,23) from n38_25175_borrow where n38_borrow_bookname='" + BookName + "' and n38_borrow_username='" + UserName + "'";
        DateTime time=DateTime.Parse(DBManager.Instance().ExecuteScalar(CommandType.Text, sqlStr).ToString().Trim());
        return time;
    }
}
