using System;
using System.Data.Common;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace bookstore
{
	/// <summary>
	/// util1 的摘要說明。
	/// </summary>
	public class util1
	{
		SqlBean sq=new SqlBean();
		public bool isExist(string sql){
		    bool et=false;
			OleDbDataReader reader=sq.selectTable(sql);

			if(reader.HasRows){
			et=true;
			}
			reader.Close();
			return et;
		}

		public void dataGridP(DataGrid d,string sql,string name){
		DataSet ds=sq.selectTable2(sql,name);
			d.SetDataBinding(ds,name);
		}
		public int getBook(string rid,string bid){
		    string sql="select * from Reader where Rid='"+rid+"'";
			string sql1="select * from Book where Bid='"+bid+"'";
			OleDbDataReader reader=sq.selectTable(sql);
			if(!reader.HasRows)
			{
				MessageBox.Show("數據庫里沒有該讀者！！");
				reader.Close();
				return 0;

			}
			else{
			reader.Close();
			}
			
			reader=sq.selectTable(sql1);
			if(!reader.HasRows)
			{
				MessageBox.Show("數據庫里沒有該圖書！！");
				reader.Close();
				return 0;
			}
			else{
			reader.Close();
			}
			string sql2="select * from GetBInfo where Rid='"+rid+"' and Bid='"+bid+"'";
			
			reader=sq.selectTable(sql2);
			if(reader.HasRows)
			{
				MessageBox.Show("該讀者已經借閱了該圖書，請先還再借！！");
				reader.Close();
				return 0;
			}
			else{
			reader.Close();
			}
			string sql3="select Reader.Rgetnum,ReaderType.Getmaxnum,ReaderType.Getmaxday from Reader,ReaderType where Reader.Rtypeno=ReaderType.Typeid and Reader.Rid='"+rid+"'";
			//SqlBean sq=new SqlBean();
			
			reader=sq.selectTable(sql3);
			reader.Read();
			 
			 
				  int yjnum=int.Parse(reader.GetValue(0).ToString());
				  int xjnum=int.Parse(reader.GetValue(1).ToString());
				 int jyqx=int.Parse(reader.GetValue(2).ToString());
			 
			 
            reader.Close();
			if(xjnum-yjnum>0)
			{
				//getbin.setGDate();
				//getbin.setSDate();
				DateTime gdate=DateTime.Today;
				DateTime sdate=gdate.AddDays(jyqx);
				string sql4="insert into GetBInfo values('"+rid+"','"+bid+"','"+gdate.ToString()+"','"+sdate.ToString()+"')"; 
				int m=sq.Update(sql4);
				int yjnum1=yjnum+1;
				string sql5="update Reader set Rgetnum="+yjnum1+" where Rid='"+rid+"'";
				//SqlBean sq1=new SqlBean();
				int n=sq.Update(sql5);
				string sql6="select Bnum from Book where Bid='"+bid+"'";
				
				reader=sq.selectTable(sql6);
				reader.Read();
				int eb=int.Parse(reader.GetValue(0).ToString());
				eb=eb-1;
				reader.Close();
				string sql7="update Book set Bnum="+eb+" where Bid='"+bid+"'";
				//SqlBean sq5=new SqlBean();
				int z=sq.Update(sql7);
				if(m==0||n==0||z==0)
				{
					MessageBox.Show("發生錯誤，借閱未成功！！");
					return 0;
				}
				else
				{
					MessageBox.Show("ok！！ 借閱成功！！！");
					return 1;
				}
			}
			else
			{
				MessageBox.Show("該讀者已經借滿，不能再借！！");
				return 0;
			}
		}
		public int setBook(string rid,string bid){
			string sql="select * from Reader where Rid='"+rid+"'";
			string sql1="select * from Book where Bid='"+bid+"'";
			OleDbDataReader reader=sq.selectTable(sql);
			if(!reader.HasRows)
			{
				MessageBox.Show("數據庫里沒有該讀者！！");
				reader.Close();
				return 0;
			}
			else{
			reader.Close();
			}
			
			reader=sq.selectTable(sql1);
			if(!reader.HasRows)
			{
				MessageBox.Show("數據庫里沒有該圖書！！");
				reader.Close();
				return 0;
			}
			else{
			reader.Close();
			}
            
			string sql2="select * from GetBInfo where Rid='"+rid+"' and Bid='"+bid+"'";
			//SqlBean sq10=new SqlBean();
			
			 reader=sq.selectTable(sql2);
			if(!reader.HasRows)
			{
				MessageBox.Show("沒有該讀者借該書！！");
				reader.Close();
				return 0;
			}
			else{
			reader.Close();
			}
			
			//GetBInfo getb=new GetBInfo();
			string sql3="select Setdate from GetBInfo where Rid='"+rid+"' and Bid='"+bid+"'";
			
			reader=sq.selectTable(sql3);
			reader.Read();
			DateTime date1=DateTime.Parse(reader.GetString(0));
			DateTime date2=DateTime.Today;
			int year=date2.Year-date1.Year;
			int month=date2.Month-date1.Month;
			int day=date2.Day-date1.Day;
			reader.Close();
			if(year>=0||month>0||day>0)
			{
				string str="此書已超期"+year.ToString()+"年";
				if(month>=0)
				{
					str=str+" 又"+month.ToString()+"個月";
				}
				else{
				str=str+" 差"+month.ToString()+"個月";
				}
				if(day>=0)
				{
					str=str+" 又"+day.ToString()+"天";
				}
				else{
				str=str+" 差"+day.ToString()+"天";
				}
				MessageBox.Show(str);
			}
			string sql4="delete from GetBInfo where Rid='"+rid+"' and Bid='"+bid+"'";
			int m=sq.Update(sql4);
			string sql5="select Rgetnum from Reader where Rid='"+rid+"'";
			//SqlBean sq=new SqlBean();
			//reader.Close();
			reader=sq.selectTable(sql5);
			reader.Read();
			 
			 
		    int yjsl=int.Parse(reader.GetValue(0).ToString());
			 
			 
            reader.Close();
			string sql6="update Reader set Rgetnum="+(yjsl-1)+" where Rid='"+rid+"'";
			//SqlBean sq1=new SqlBean();
			int n=sq.Update(sql6);
			string sql7="select Bnum from Book where Bid='"+bid+"'";
			//SqlBean sq6=new SqlBean();
			
			reader=sq.selectTable(sql7);
			reader.Read();
			
			int bnum1=int.Parse(reader.GetValue(0).ToString());
			reader.Close();
			bnum1=bnum1+1;
			string sql8="update Book set Bnum="+bnum1+" where Bid='"+bid+"'";
			//SqlBean sq8=new SqlBean();
			int z=sq.Update(sql8);
			
			if(m==0||n==0||z==0)
			{
				MessageBox.Show("還書過程中發生錯誤！！");
				return 0;
			}
			else
			{
				MessageBox.Show("ok！！ 還書成功！！！");
				return 1;
			}
		}

		public bool dayformMonth(int year,int month,int day){
			bool b=false;
			bool b1=true;
			if(year%4==0 && year%100==0){
				b=true;
			}
			switch(month){
				case 1:
				case 3:
				case 5:
				case 7:
				case 8:
				case 10:
				case 12:
					if(day>31) b1=false;break;
				case 2:
					if(b)
					{
						if(day>28) b1=false;break;
					}
					else{
					if(day>29) b1=false;break;
					}
				case 4:
				case 6:
				case 9:
				case 11:
					if(day>30) b1=false;break;
			}
			return b1;
		}
		public util1()
		{
			//
			// TODO: 在此處添加構造函數邏輯
			//
		}
	}
}