using System;

namespace Library_BS
{
	/// <summary>
	/// Borrow 的摘要說明。
	/// </summary>
	public class Borrow
	{
		//判斷是非有圖書超期
		public int GetReturn() 
		{
			string connectionString = "server=\'(local)\'; trusted_connection=true; database=\'Library\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "select count(*) from BookDuplication where ShouldReturnTime<=getdate()";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			dbConnection.Open();
			//  System.Data.IDataReader dataReader = dbCommand.ExecuteScalar();
			int temp =(int) dbCommand.ExecuteScalar();
        
			return temp;

		}
		//查找罰款信息
	    
		
		public System.Data.IDataReader GetFineInfo(string userBarCode) 
		{
			string connectionString = "server=\'(local)\'; trusted_connection=true; database=\'Library\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "SELECT [Fines].[FineMoney] FROM [Fines] WHERE ([Fines].[UserBarCode] = @UserBarCo" +
				"de)";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_userBarCode = new System.Data.SqlClient.SqlParameter();
			dbParam_userBarCode.ParameterName = "@UserBarCode";
			dbParam_userBarCode.Value = userBarCode;
			dbParam_userBarCode.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_userBarCode);
        
			dbConnection.Open();
			System.Data.IDataReader dataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        
			return dataReader;
		}// Insert page code here
		//得到續借的狀態
		
		public System.Data.IDataReader GetRenewTimes(int bookID) 
		{
			string connectionString = "server=\'(local)\'; trusted_connection=true; database=\'Library\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "SELECT [BookDuplication].[RenewTimes] FROM [BookDuplication] WHERE ([BookDuplicat" +
				"ion].[BookID] = @BookID)";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_bookID = new System.Data.SqlClient.SqlParameter();
			dbParam_bookID.ParameterName = "@BookID";
			dbParam_bookID.Value = bookID;
			dbParam_bookID.DbType = System.Data.DbType.Int32;
			dbCommand.Parameters.Add(dbParam_bookID);
        
			dbConnection.Open();
			System.Data.IDataReader dataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        
			return dataReader;
		}// Insert page code here
		//續借更新圖書副本表
		public int MyUpdatexjtufb(int bookID, string userBarCode, System.DateTime shouldReturnTime, int renewTimes) 
		{
			//int RowsAffected=borrow.MyUpdatexjtufb(BookID,this.txtBbar.Text,day,1);
			string connectionString = "server=\'(local)\'; trusted_connection=true; database=\'Library\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "UPDATE [BookDuplication] SET [ShouldReturnTime]=@ShouldReturnTime, [RenewTimes]=@" +
				"RenewTimes WHERE (([BookDuplication].[BookID] = @BookID) AND ([BookDuplication]." +
				"[UserBarCode] = @UserBarCode))";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_bookID = new System.Data.SqlClient.SqlParameter();
			dbParam_bookID.ParameterName = "@BookID";
			dbParam_bookID.Value = bookID;
			dbParam_bookID.DbType = System.Data.DbType.Int32;
			dbCommand.Parameters.Add(dbParam_bookID);
			System.Data.IDataParameter dbParam_userBarCode = new System.Data.SqlClient.SqlParameter();
			dbParam_userBarCode.ParameterName = "@UserBarCode";
			dbParam_userBarCode.Value = userBarCode;
			dbParam_userBarCode.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_userBarCode);
			System.Data.IDataParameter dbParam_shouldReturnTime = new System.Data.SqlClient.SqlParameter();
			dbParam_shouldReturnTime.ParameterName = "@ShouldReturnTime";
			dbParam_shouldReturnTime.Value = shouldReturnTime;
			dbParam_shouldReturnTime.DbType = System.Data.DbType.DateTime;
			dbCommand.Parameters.Add(dbParam_shouldReturnTime);
			System.Data.IDataParameter dbParam_renewTimes = new System.Data.SqlClient.SqlParameter();
			dbParam_renewTimes.ParameterName = "@RenewTimes";
			dbParam_renewTimes.Value = renewTimes;
			dbParam_renewTimes.DbType = System.Data.DbType.Int32;
			dbCommand.Parameters.Add(dbParam_renewTimes);
        
			int rowsAffected = 0;
			dbConnection.Open();
			try 
			{
				rowsAffected = dbCommand.ExecuteNonQuery();
			}
			finally 
			{
				dbConnection.Close();
			}
        
			return rowsAffected;
		}// Insert page code here

	}
}