package BeanProcess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import DatabaseConnect.ConnectDB;
import model.Company;

public class CompanyPro {

	private Connection ct = null;
	private ResultSet rs = null;
	private PreparedStatement sta = null;
	
//	新加的
	private int rowCount;
	private int pageSize=10;
	private int pageCount;
	//user login method
	//获取页数
	public int getPageCount() {
		String sql = "select count(*) from company";
		try {
			ct = new ConnectDB().getConn();
			sta=ct.prepareStatement(sql);
			rs = sta.executeQuery();
			if (rs.next()) {
				rowCount = rs.getInt(1);
			}
			if (rowCount % pageSize == 0) {
				pageCount = rowCount / pageSize;
			} else {
				pageCount = rowCount / pageSize + 1;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.closeM();
		}
		return pageCount;
	}
	
	public ArrayList<Company> getCompanysPageByID(int pageNow,int typeid) {
		ArrayList<Company> al = new ArrayList<Company>();
		String sql = "select * from company where typeid = '"+typeid+"' limit "+ (pageNow-1)*pageSize+","+pageSize;
		try {
			ct = new ConnectDB().getConn();
			sta = ct.prepareStatement(sql);
			rs = sta.executeQuery();
			while (rs.next()) {
				Company company = new Company();
				company.setCompanyID(rs.getInt("companyid"));
				company.setCompanyName(rs.getString("companyname"));
				company.setAddress(rs.getString("address"));
				company.setTypeID(rs.getInt("typeid"));
				company.setMonitorName(rs.getString("monitorname"));
				company.setPhone(rs.getString("cellphone"));
				al.add(company); // 
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.closeM();
		}
		return al;
	}
	
	/**
	 * @param pageNow
	 * @return
	 */
	public ArrayList<Company> getCompanysByPage(int pageNow) {
		ArrayList<Company> al = new ArrayList<Company>();
		String sql = "select * from company limit "+ (pageNow-1)*pageSize+","+pageSize;
		try {
			ct = new ConnectDB().getConn();
			sta = ct.prepareStatement(sql);
			rs = sta.executeQuery();
			while (rs.next()) {
				Company company = new Company();
				company.setCompanyID(rs.getInt("companyid"));
				company.setCompanyName(rs.getString("companyname"));
				company.setAddress(rs.getString("address"));
				company.setTypeID(rs.getInt("typeid"));
				company.setMonitorName(rs.getString("monitorname"));
				company.setPhone(rs.getString("cellphone"));
				al.add(company); // 
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.closeM();
		}
		return al;
	}
	
	public int getCompanyID(String companyName) {
		int id = 0;
		String sql = "select companyid from company where companyname = '"+companyName+"'";
		try {
			ConnectDB cdb = new ConnectDB();
			ct = cdb.getConn();
			sta = ct.prepareStatement(sql);
			rs = sta.executeQuery();;
			if (rs.next()) {
				id = rs.getInt("companyid");
			}
		} catch (Exception ex) {
			// TODO: handle exception
			ex.printStackTrace();
		}finally{
			this.closeM();
		}
		return id;
	}
	//新加的
	public boolean delCompany(String companyId) {
		// TODO Auto-generated method stub
		boolean b = false;
		String sql = "delete from company where companyid = '"+companyId+"'";
		try {
			ConnectDB cdb = new ConnectDB();
			ct = cdb.getConn();
			sta = ct.prepareStatement(sql);
			int a = sta.executeUpdate();
			if (a == 1) {
				b = true;
			}
		} catch (Exception ex) {
			// TODO: handle exception
			ex.printStackTrace();
		}finally{
			this.closeM();
		}
		return b;
	}

	//get all company
	public ArrayList<Company> getAllCompanies() {
		ArrayList<Company> companyList = new ArrayList<Company>();
		String sql = "select * from company";
		try {
			ConnectDB cdb = new ConnectDB();
			ct = cdb.getConn();
			sta = ct.prepareStatement(sql);
			rs = sta.executeQuery();
			while (rs.next()) {
				Company company = new Company();
				company.setCompanyID(rs.getInt("companyid"));
				company.setCompanyName(rs.getString("companyname"));
				company.setAddress(rs.getString("address"));
				company.setTypeID(rs.getInt("typeid"));
				company.setMonitorName(rs.getString("monitorname"));
				company.setPhone(rs.getString("cellphont"));
				companyList.add(company);
			}
		} catch (Exception ex) {
			// TODO: handle exception
			ex.printStackTrace();
		}finally{
			this.closeM();
		}		
		return companyList;
	}
	
	public int getTypeIDByID(int companyid) {
		int id = 0;
		String sql = "select typeid from company where companyid='"+companyid+"'";
		try {
			ConnectDB cdb = new ConnectDB();
			ct = cdb.getConn();
			sta = ct.prepareStatement(sql);
			rs = sta.executeQuery();
			if (rs.next()) {
				id = rs.getInt("typeid");
			}
		} catch (Exception ex) {
			// TODO: handle exception
			ex.printStackTrace();
		}finally{
			this.closeM();
		}
		return id;
	}
	
	public int getIDByName(String name) {
		int id = 0;
		String sql = "select companyid from company where companyname='"+name+"'";
		try {
			ConnectDB cdb = new ConnectDB();
			ct = cdb.getConn();
			sta = ct.prepareStatement(sql);
			rs = sta.executeQuery();
			if (rs.next()) {
				id = rs.getInt("companyid");
			}
		} catch (Exception ex) {
			// TODO: handle exception
			ex.printStackTrace();
		}finally{
			this.closeM();
		}
		return id;
	}
	
	public String getNameByID(int companyid) {
		String name = "";
		String sql = "select companyname from company where companyid ='"+companyid+"'";
		try {
			ConnectDB cdb = new ConnectDB();
			ct = cdb.getConn();
			sta = ct.prepareStatement(sql);
			rs = sta.executeQuery();
			if (rs.next()) {
				name = rs.getString("companyname");
			}
		} catch (Exception ex) {
			// TODO: handle exception
			ex.printStackTrace();
		}finally{
			this.closeM();
		}
		System.out.println("CompanyPro getName Test: "+name);
		return name;
	}
	
	//get company by id
	public ArrayList<Company> getCompanyByID(int id) {
		ArrayList<Company> companyList = new ArrayList<Company>();
		String sql = "select * from company where companyid='"+id+"'";
		try {
			ConnectDB cdb = new ConnectDB();
			ct = cdb.getConn();
			sta = ct.prepareStatement(sql);
			rs = sta.executeQuery();
			while (rs.next()) {
				Company company = new Company();
				company.setCompanyID(rs.getInt("companyid"));
				company.setCompanyName(rs.getString("companyname"));
				company.setAddress(rs.getString("address"));
				company.setTypeID(rs.getInt("typeid"));
				company.setMonitorName(rs.getString("monitorname"));
				company.setPhone(rs.getString("cellphone"));
				companyList.add(company);
			}
		} catch (Exception ex) {
			// TODO: handle exception
			ex.printStackTrace();
		}finally{
			this.closeM();
		}		
		return companyList;
	}
	
	//add company
	public boolean addCompany(String companyname,String address,int typeid, String name,String cellphone) {
		boolean b = false;
		String sql = "insert into company (companyname,address,typeid,monitorname,cellphone) values('"+companyname+"','"+address+"','"+typeid+"','"+name+"','"+cellphone+"')";
		try {
			ConnectDB cdb = new ConnectDB();
			ct = cdb.getConn();
			sta = ct.prepareStatement(sql);
			int a = sta.executeUpdate();
			if (a == 1) {
				b = true;
			}
		} catch (Exception ex) {
			// TODO: handle exception
			ex.printStackTrace();
		}finally{
			this.closeM();
		}
		return b;
	}

	//update company by id
	public boolean updateCompany(int id,String companyname,String address,int typeid, String monitorname, String cellphone) {
		boolean b = false;
		String sql = "UPDATE company SET companyname = '"+companyname+"', address = '"+address+"' , typeid = '"+typeid+"', monitorname = '"+monitorname+"', cellphone = '"+cellphone+"' WHERE companyid = '"+id+"'";
		try {
			ConnectDB cdb = new ConnectDB();
			ct = cdb.getConn();
			sta = ct.prepareStatement(sql);
			int a = sta.executeUpdate();
			if (a == 1) {
				b = true;
			}
		} catch (Exception ex) {
			// TODO: handle exception
			ex.printStackTrace();
		}finally{
			this.closeM();
		}
		return b;
	}
	
	public void closeM() {
		if (rs!=null) {
			try {
				rs.close();
				rs = null;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (ct!=null) {
			try {
				ct.close();
				ct = null;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (sta!=null) {
			try {
				sta.close();
				sta = null;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
}