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_GetDataSet 的摘要说明
/// </summary>
public class N38_GetDataSet
{
	public N38_GetDataSet()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}

    /// <summary>
    /// 用正则表达式验证字符串
    /// </summary>
    /// <param name="express"></param>
    /// <param name="value"></param>
    /// <returns></returns>
    public bool QuickValidate(string express, string value)
    {
        if (value == null) return false;
        System.Text.RegularExpressions.Regex myRegex = new System.Text.RegularExpressions.Regex(express);
        if (value.Length == 0)
        {
            return false;
        }
        return myRegex.IsMatch(value);
    } 

    /// <summary>
    /// 获取catalog表记录条数
    /// </summary>
    /// <returns></returns>
    public int GetCatalogCount()
    {
        string sqlStr = "select count(*) from n38_25175_catalog";
        int num = int.Parse(DBManager.Instance().ExecuteScalar(CommandType.Text, sqlStr).ToString());
        return num;
    }

    /// <summary>
    /// 获取catalog表数据集
    /// </summary>
    /// <param name="pageindex"></param>
    /// <param name="pagesize"></param>
    /// <returns></returns>
    public DataSet GetCatalogData(int pageindex, int pagesize)
    {
        string sqlStr="select * from n38_25175_catalog";
        DataSet ds = DBManager.Instance().ExecuteDataSet2(CommandType.Text, sqlStr, pageindex, pagesize, "n38_25175_catalog");
        return ds;
    }
    /// <summary>
    /// 获取book表全部数据条数
    /// </summary>
    /// <returns></returns>
    public int GetBookCount()
    {
        string sqlStr = "select count(*) from n38_25175_book";
        int num = int.Parse(DBManager.Instance().ExecuteScalar(CommandType.Text, sqlStr).ToString());
        return num;
    }
    /// <summary>
    /// 获取book表全部数据集
    /// </summary>
    /// <param name="pageindex"></param>
    /// <param name="pagesize"></param>
    /// <returns></returns>
    public DataSet GetBookData(int pageindex, int pagesize)
    {
        string sqlStr = "select * from n38_25175_book";
        DataSet ds = DBManager.Instance().ExecuteDataSet2(CommandType.Text, sqlStr, pageindex, pagesize, "n38_25175_book");
        return ds;
    }

    /// <summary>
    /// 获取book表查询数据条数
    /// </summary>
    /// <returns></returns>
    public int GetBookSearchCount(TextBox BookName, DropDownList Catalog)
    {
        string sb = "select count(*) from n38_25175_book ";
        if (Catalog.SelectedValue != "请选择")
        {
            string catalog = Catalog.SelectedValue;
            sb = sb + "where n38_book_catalog='" + catalog + "'";
            if (BookName.Text != "")
            {
                sb = sb + "and n38_book_name like '%" + BookName.Text.Trim() + "%'";
            }
        }
        else
        {
            if (BookName.Text != "")
            {
                sb = sb + "where n38_book_name like '%" + BookName.Text.Trim() + "%'";
            }
        }
        int num = int.Parse(DBManager.Instance().ExecuteScalar(CommandType.Text, sb).ToString().Trim());
        return num;
    }
    /// <summary>
    /// 获取book表查询数据集
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="BookName"></param>
    /// <param name="Catalog"></param>
    /// <param name="ISBN"></param>
    /// <param name="pageindex"></param>
    /// <param name="pagesize"></param>
    /// <returns></returns>
    public DataSet GetBookSearchData(TextBox BookName, DropDownList Catalog, int pageindex, int pagesize)
    {
        DataSet ds = new DataSet();
        string sb = "select * from n38_25175_book ";
        if (Catalog.SelectedValue != "请选择")
        {
            string catalog = Catalog.SelectedValue;
            sb = sb + "where n38_book_catalog='" + catalog + "'";
            if (BookName.Text != "")
            {
                sb = sb + "and n38_book_name like '%" + BookName.Text.Trim() + "%'";
            }
        }
        else
        {
            if (BookName.Text != "")
            {
                sb = sb + "where n38_book_name like '%" + BookName.Text.Trim() + "%'";
            }
        }
        ds = DBManager.Instance().ExecuteDataSet2(CommandType.Text, sb, pageindex, pagesize, "n38_25175_book");
        return ds;
    }

    /// <summary>
    /// 根据id获取图书信息
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public DataSet GetBookByID(int ID)
    {
        string sqlStr = "select * from n38_25175_book where n38_book_id=" + ID + "";
        DataSet ds = DBManager.Instance().ExecuteDataSet(CommandType.Text, sqlStr);
        return ds;
    }

    /// <summary>
    /// 根据书名获取图书信息
    /// </summary>
    /// <param name="ID"></param>
    /// <returns></returns>
    public DataSet GetBookByName(string BookName)
    {
        string sqlStr = "select * from n38_25175_book where n38_book_name like '%" + BookName + "%'";
        DataSet ds = DBManager.Instance().ExecuteDataSet(CommandType.Text, sqlStr);
        return ds;
    }

    /// <summary>
    /// 用户还书数据集
    /// </summary>
    /// <param name="UserName"></param>
    /// <param name="BookName"></param>
    /// <returns></returns>
    public DataSet GetBorrowBookData(string UserName,string BookName)
    {
        string sqlStr = "select * from n38_25175_borrow where n38_borrow_username='" + UserName + "'and n38_borrow_bookname='" + BookName + "'";
        DataSet ds = DBManager.Instance().ExecuteDataSet(CommandType.Text, sqlStr);
        return ds;
    }
    /// <summary>
    /// 获取用户借书记录
    /// </summary>
    /// <param name="UserName"></param>
    /// <returns></returns>
    public DataSet GetUserBorrowData(string UserName)
    {
        string sqlStr = "select * from n38_25175_borrow where n38_borrow_username='" + UserName + "'";
        DataSet ds = DBManager.Instance().ExecuteDataSet(CommandType.Text, sqlStr);
        return ds;
    }
    /// <summary>
    /// 获取指定用户的借书记录
    /// </summary>
    /// <param name="UserName"></param>
    /// <returns></returns>
    public DataSet GetLostBookData(string UserName)
    {
        string sqlStr = "select * from n38_25175_borrow where n38_borrow_username='" + UserName + "'";
        DataSet ds = DBManager.Instance().ExecuteDataSet(CommandType.Text, sqlStr);
        return ds;
    }

    /// <summary>
    /// 取消挂失数据集
    /// </summary>
    /// <param name="UserName"></param>
    /// <param name="BookName"></param>
    /// <returns></returns>
    public DataSet GetFindBookData(string UserName,string BookName)
    {
        string sqlStr = "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 where n38_lost_username='" + UserName + "' and n38_lost_bookname='" + BookName + "'";
        DataSet ds = DBManager.Instance().ExecuteDataSet(CommandType.Text, sqlStr);
        return ds;
    }

    /// <summary>
    /// 获取borrow表记录条数
    /// </summary>
    /// <returns></returns>
    public int GetBorrowCount()
    {
        string sqlStr = "select count(*) from n38_25175_borrow";
        int num = int.Parse(DBManager.Instance().ExecuteScalar(CommandType.Text, sqlStr).ToString());
        return num;
    }

    /// <summary>
    /// 获取borrow表全部数据集
    /// </summary>
    /// <param name="pageindex"></param>
    /// <param name="pagesize"></param>
    /// <returns></returns>
    public DataSet GetBorrowData(int pageindex, int pagesize)
    {
        string sqlStr = "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 from n38_25175_borrow join n38_25175_user on n38_25175_borrow.n38_borrow_username=n38_25175_user.n38_user_name";
        DataSet ds = DBManager.Instance().ExecuteDataSet2(CommandType.Text, sqlStr, pageindex, pagesize, "n38_25175_borrow");
        return ds;
    }

    /// <summary>
    /// 获取lost表数据条数
    /// </summary>
    /// <returns></returns>
    public int GetLostCount()
    {
        string sqlStr = "select count(*) from n38_25175_lost";
        int num = int.Parse(DBManager.Instance().ExecuteScalar(CommandType.Text, sqlStr).ToString());
        return num;
    }
    /// <summary>
    /// 获取lost表全部数据集
    /// </summary>
    /// <param name="UserName"></param>
    /// <param name="BookName"></param>
    /// <returns></returns>
    public DataSet GetlostData(int pageindex,int pagesize)
    {
        string sqlStr = "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";
        DataSet ds = DBManager.Instance().ExecuteDataSet2(CommandType.Text, sqlStr, pageindex, pagesize, "n38_25175_lost");
        return ds;
    }

    /// <summary>
    /// 获取user表记录数
    /// </summary>
    /// <returns></returns>
    public int GetUserCount(TextBox RealName,DropDownList State)
    {
        string sqlStr = "select count(*) from n38_25175_user";
        if (RealName.Text != "")
        {
            sqlStr = sqlStr + " where n38_user_realname='" + RealName.Text.Trim() + "'";
            if (State.SelectedValue != "请选择")
            {
                sqlStr = sqlStr + " and n38_user_state='" + State.SelectedValue + "'";
            }
        }
        else
        {
            if (State.SelectedValue != "请选择")
            {
                sqlStr = sqlStr + " where n38_user_state='" + State.SelectedValue + "'";
            }
        }
        int num = int.Parse(DBManager.Instance().ExecuteScalar(CommandType.Text, sqlStr).ToString());
        return num;
    }

    /// <summary>
    /// 获取user表全部数据集
    /// </summary>
    /// <param name="pageindex"></param>
    /// <param name="pagesize"></param>
    /// <returns></returns>
    public DataSet GetUserData(TextBox RealName,DropDownList State, int pageindex, int pagesize)
    {
        string sqlStr = "select * from n38_25175_user";
        if (RealName.Text != "")
        {
            sqlStr = sqlStr + " where n38_user_realname='" + RealName.Text.Trim() + "'";
            if (State.SelectedValue != "请选择")
            {
                sqlStr = sqlStr + " and n38_user_state='" + State.SelectedValue + "'";
            }
        }
        else
        {
            if (State.SelectedValue != "请选择")
            {
                sqlStr = sqlStr + " where n38_user_state='" + State.SelectedValue + "'";
            }
        }
        DataSet ds = DBManager.Instance().ExecuteDataSet2(CommandType.Text, sqlStr, pageindex, pagesize, "n38_25175_user");
        return ds;
    }

    /// <summary>
    /// 获取break表全部记录条数
    /// </summary>
    /// <returns></returns>
    public int GetBreakCount()
    {
        string sqlStr = "select count(*) from n38_25175_break";
        int num = int.Parse(DBManager.Instance().ExecuteScalar(CommandType.Text, sqlStr).ToString());
        return num;
    }
    
    /// <summary>
    /// 获取break表全部记录数据集
    /// </summary>
    /// <param name="pageindex"></param>
    /// <param name="pagesize"></param>
    /// <returns></returns>
    public DataSet GetBreakData(int pageindex, int pagesize)
    {
        string sqlStr = "select * from n38_25175_break";
        DataSet ds = DBManager.Instance().ExecuteDataSet2(CommandType.Text, sqlStr, pageindex, pagesize, "n38_25175_break");
        return ds;
    }

    /// <summary>
    /// 超期表全部记录条数
    /// </summary>
    /// <returns></returns>
    public int GetOverTimeCount()
    {
        string sqlStr = "select count(*) from n38_25175_overtime";
        int num = int.Parse(DBManager.Instance().ExecuteScalar(CommandType.Text, sqlStr).ToString());
        return num;
    }

    /// <summary>
    /// 超期表全部记录数据集
    /// </summary>
    /// <param name="pageindex"></param>
    /// <param name="pagesize"></param>
    /// <returns></returns>
    public DataSet GetOverTimeData(int pageindex, int pagesize)
    {
        string sqlStr = "select n38_25175_overtime.n38_overtime_bookname,n38_25175_overtime.n38_overtime_borrowtime,n38_25175_user.n38_user_realname from n38_25175_overtime join n38_25175_user on n38_25175_overtime.n38_overtime_username=n38_25175_user.n38_user_name";
        DataSet ds = DBManager.Instance().ExecuteDataSet2(CommandType.Text, sqlStr, pageindex, pagesize, "n38_25175_break");
        return ds;
    }
}
