package com.lib.db.oper;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import com.lib.DBConnection;
import com.lib.db.Detail;

public class DetailOper {
	public static Connection con = DBConnection.getConnection();

	//用flag來標(biāo)識(shí)查找方式
	//0----查找全部 no use
	//1----查找借閱者的 未歸還書 記錄(傳入借閱者id)
	//2----查找借閱者的 所有 記錄(傳入借閱者id)
	//3----根據(jù)流水號(hào)查找(傳入detail_id)
	public static Vector getInfo(int flag,String condition) {
		Vector v = new Vector();
		Statement stmt = null;
		String querySQL = null;
		try {
			stmt = con.createStatement();
			if(flag == 0)	querySQL = "select * from lib_deta order by to_number(detail_id)";
			if(flag == 1)	querySQL = "select * from lib_deta where borrower_id='"+condition+"' and isReturn='0' order by to_number(detail_id)";
			if(flag == 2)	querySQL = "select * from lib_deta where borrower_id='"+condition+"' order by to_number(detail_id)";
			if(flag == 3)	querySQL = "select * from lib_deta where detail_id='"+condition+"' order by to_number(detail_id)";
			
			
			System.out.println("~~~~~~~~~~" + querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			while (set.next()) {					
				String detail_id = set.getString("detail_id");
				String borrower_id = set.getString("borrower_id");
				String isbn = set.getString("isbn");
				Date borrow_date = set.getDate("borrow_date");
				String isRenew = set.getString("isRenew");
				Date renew_date = set.getDate("renew_date");
				String isReturn = set.getString("isReturn");
				Date return_date = set.getDate("return_date");
				Float fine = set.getFloat("fine");
				String borrower = set.getString("borrower");
				String book = set.getString("book");
				v.addElement(new Detail(detail_id, borrower_id,isbn,borrow_date,isRenew,renew_date,isReturn,return_date,fine,borrower,book));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();				
			}
		}
		return v;
	}
	
	//查詢 當(dāng)前借閱者 要提醒 催還的書
	/**
	 * id -當(dāng)前借閱者id
	 * down - 條件查詢時(shí) 日期差下限
	 * up - 條件查詢時(shí) 日期差上限
	 */
	public static Vector getUrgeInfo(String id,int down,int up) {
		Vector v = new Vector();
		Statement stmt = null;
		String querySQL = null;
		try {
			stmt = con.createStatement();
			querySQL = "select * from lib_deta  " +
						"where borrower_id = '"+id+
						"' and " +
							"(isRenew = '0' and (sysdate - borrow_date) between "+down+" and "+up+"" +
							" or " +
							"isRenew = '1' and (sysdate - renew_date) between "+down+" and "+up+")";			
			
			System.out.println("~~~~~getUrgeInfo~~~~~" + querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			while (set.next()) {					
				String detail_id = set.getString("detail_id");
				String borrower_id = set.getString("borrower_id");
				String isbn = set.getString("isbn");
				Date borrow_date = set.getDate("borrow_date");
				String isRenew = set.getString("isRenew");
				Date renew_date = set.getDate("renew_date");
				String isReturn = set.getString("isReturn");
				Date return_date = set.getDate("return_date");
				Float fine = set.getFloat("fine");
				String borrower = set.getString("borrower");
				String book = set.getString("book");
				v.addElement(new Detail(detail_id, borrower_id,isbn,borrow_date,isRenew,renew_date,isReturn,return_date,fine,borrower,book));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();				
			}
		}
		return v;
	}
	

	//新增一條記錄
	public static boolean add(Detail value) {
		boolean flag = false;
		String insertSQL = "insert into lib_detail(detail_id, borrower_id,isbn,borrow_date,isRenew,renew_date,isReturn,return_date) values(to_char(lib_detail_id.nextval),?,?,?,?,?,?,?)";

		PreparedStatement psmt = null;
		int rows = 0;
		try {			
			psmt = con.prepareStatement(insertSQL);
			psmt.setString(1, value.getBorrower_id());
			psmt.setString(2, value.getIsbn());
			psmt.setDate(3, value.getBorrow_date());
			psmt.setString(4, value.getIsRenew());	
			psmt.setDate(5, value.getRenew_date());			
			psmt.setString(6, value.getIsReturn());
			psmt.setDate(7, value.getReturn_date());
			rows = psmt.executeUpdate();
			if (rows != 0) {
				flag = true;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();			
		} finally {
			try {
				psmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();				
			}
		}
		return flag;
	}

	
	/**
	 * 未用到
	 * @param id
	 * @return
	 */
	public static boolean delete(String id) {
		// Connection con = DBConnection.getConnection();
		String deleteSQL = "delete from lib_detail where detail_id=? ";
		PreparedStatement psmt = null;
		try {
			psmt = con.prepareStatement(deleteSQL);
			psmt.setString(1, id);
			psmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				psmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();				
			}
		}
		return true;
	}

	//用于 還書流程
	public static boolean update(Detail value) {		
		String updateSQL = "update  lib_detail  set borrower_id=?,isbn=?,borrow_date=?,isRenew=?,renew_date=?,isReturn=?,return_date=?,fine=? where detail_id=?";
		PreparedStatement psmt = null;
		try {			
			psmt = con.prepareStatement(updateSQL);
			psmt.setString(1, value.getBorrower_id());
			psmt.setString(2, value.getIsbn());
			psmt.setDate(3, value.getBorrow_date());
			psmt.setString(4, value.getIsRenew());
			psmt.setDate(5,  value.getRenew_date());
			psmt.setString(6, value.getIsReturn());
			psmt.setDate(7,value.getReturn_date());
			psmt.setFloat(8,value.getFine());
			psmt.setString(9, value.getDetail_id());
			psmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				psmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();			
			}
		}
		return true;
	}
	
	//當(dāng)借閱者已在近期(5天內(nèi))借閱記錄上 借過此書,并歸還
	//此時(shí),要作特殊處理
	//若未續(xù)借,則說明此次借書為 續(xù)借!!!!
	//若已續(xù)借,則借閱者 在5天內(nèi)不可再續(xù)借!!!
	
	
	//查詢此書在5天前是否已經(jīng)續(xù)借過并歸還,已保證 借閱者不能再續(xù)借
	//用于借書時(shí)  
	//檢查 是否在 續(xù)借緩沖期 返回 借閱者可借書之日
	public static Date getActiveDate(String isbn,String borrower_id) {
		Statement stmt = null;
		Date active = null;
		try {
			stmt = con.createStatement();
			String querySQL = "select 5 + return_date active from lib_deta where isbn = '"+isbn+"' and borrower_id = '" + borrower_id + "' and isrenew = '1' and (sysdate - return_date) between 0 and 5";
			System.out.println(querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			if (set.next()) {
				active = set.getDate("active");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return active;	
	}
	
	//查詢此書是否已經(jīng)續(xù)借過并未歸還,已保證 借閱者不能再借
	public static boolean isRenewed(String isbn,String borrower_id,int upDate) {
		Statement stmt = null;
		boolean flag = false;
		try {
			stmt = con.createStatement();
			String querySQL = "select detail_id from lib_deta where isbn = '"+isbn+"' and borrower_id = '" + borrower_id + "' and isrenew = '1' and (sysdate - renew_date) between 0 and " + upDate;
			System.out.println(querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			if (set.next()) {
				flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return flag;	
	}
	
	
	//如果 此書在5天前歸還了 (或者未歸還),且未續(xù)借過,則說明此次借書為 續(xù)借
	//這里只處理 5天前歸還了的情況
	//返回流水號(hào)
	public static String isRenewAction0(String isbn,String borrower_id) {
		Statement stmt = null;
		String detail_id = null;
		try {
			stmt = con.createStatement();
			String querySQL = "select detail_id from lib_deta where isbn = '"+isbn+"' and borrower_id = '" + borrower_id + "' and isrenew = '0' and (sysdate - return_date) between 0 and 5";
			System.out.println(querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			if (set.next()) {
				detail_id = set.getString("detail_id");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return detail_id;	
	}
	
	//如果 此書在5天前歸還了 (或者未歸還),且未續(xù)借過,則說明此次借書為 續(xù)借
	//這里只處理 未歸還的情況
	//返回流水號(hào)
	public static String isRenewAction(String isbn,String borrower_id,int upDate) {
		Statement stmt = null;
		String detail_id = null;
		try {
			stmt = con.createStatement();
			String querySQL = "select detail_id from lib_deta where isbn = '"+isbn+"' and borrower_id = '" + borrower_id + "' and " +
							"isRenew = '0' and (sysdate - borrow_date) between 0 and "+upDate;	
			
			System.out.println(querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			if (set.next()) {
				detail_id = set.getString("detail_id");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return detail_id;	
	}
	
	//得到服務(wù)器系統(tǒng)時(shí)間
	public static Date getSysdate() {
		Statement stmt = null;
		Date sysdate = null;
		try {
			stmt = con.createStatement();
			String querySQL = "select sysdate from dual";
			System.out.println(querySQL);
			ResultSet set = stmt.executeQuery(querySQL);
			if (set.next()) {
				sysdate = set.getDate("sysdate");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				// con.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
		}
		return sysdate;	
	}
}