package hall;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Vector;
//import javax.servlet.http.HttpServletRequest;
import java.util.*;

public class Eshop {
	private Customers user = new Customers(); //新的用戶對象

	//private javax.servlet.http.HttpServletRequest request; 
	private Vector userlist; //顯示用戶列表向量數組

	private int page = 1; //顯示的頁碼

	private int pageSize = 8; //每頁顯示的圖書數

	private int pageCount = 0; //頁面總數

	private long recordCount = 0; //查詢的記錄總數

	private String message = ""; //出錯信息提示

	private String username = ""; //注冊后返回的用戶名

	private DBWrapper myConnection = null;

	private String sqlStr = "";

	public Eshop() throws Exception {
		myConnection = DBWrapper.Instance();
	}

	
	public int checkUserName(String inName) throws Exception {
		int flag = 3;// 1 represents admin,2 represents customer,3 represents
						// that the usename isn't exsited
		sqlStr = "select * from administrators where username = '" + inName + "'";
		ResultSet rs = myConnection.runQuery(sqlStr);

		if (rs.next()) {

			flag = 1;
		} else {
			sqlStr = "select * from customers where name = '" + inName + "'";
			rs = myConnection.runQuery(sqlStr);
			if (rs.next()) {
				flag = 2;
			} else {
				flag = 3;
			}
		}
		rs.close();
		return flag;
	}

	public boolean addUser(Customers inUser) {
		try {
			String sql = "INSERT INTO customers VALUES ('" + inUser.getName()
					+ "','" + inUser.getPassword() + "','" + inUser.getEmail()
					+ "','" + inUser.getSex() + "','" + inUser.getPhone()
					+ "','" + inUser.getMobilePhone() + "','"
					+ inUser.getState() + "','" + inUser.getProvince() + "','"
					+ inUser.getCity() + "','" + inUser.getStreet() + "',"
					+ inUser.getAge() + "," + inUser.getAccount() + ")";
			//System.out.println(sql);
			myConnection.runUpdate(sql);
			return true;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return false;
		}
	}

	//查詢書店所有的用戶
	public boolean get_alluser() throws Exception {
		sqlStr = "select count(*) from customers"; //取出記錄數
		int rscount = pageSize;
		try {
			ResultSet rs1 = myConnection.runQuery(sqlStr);
			if (rs1.next())
				recordCount = rs1.getInt(1);
			System.out.println(recordCount);
			rs1.close();
		} catch (SQLException e) {
			System.out.print("count:" + e.getMessage());
			return false;
		}
		//設定有多少pageCount
		if (recordCount < 1)
			pageCount = 0;
		else
			pageCount = (int) (recordCount - 1) / pageSize + 1;
		//檢查查看的頁面數是否在范圍內
		if (page < 1)
			page = 1;
		else if (page > pageCount)
			page = pageCount;
		rscount = (int) recordCount % pageSize; // 最后一頁記錄數
		//sql為倒序取值
		sqlStr = "select  * from customers order by name";

		try {

			ResultSet rs = myConnection.runQuery(sqlStr);
			userlist = new Vector();
			if (page == 1) {

			} else {
				for (int i = 0; i < pageSize * (page - 1); i++) {
					rs.next();
				}

				for (int i = 0; i < pageSize; i++) {
					if (rs.next()) {
						User user = new User();
						user.setName(rs.getString("name"));
						user.setPassword(rs.getString("passwd"));
						user.setEmail(rs.getString("email"));
						user.setSex(rs.getString("sex"));
						user.setPhone(rs.getString("phone"));
						user.setMobilePhone(rs.getString("mobilePhone"));
						user.setState(rs.getString("state"));
						user.setProvince(rs.getString("province"));
						user.setCity(rs.getString("city"));
						user.setStreet(rs.getString("street"));
						user.setAge(rs.getInt("age"));
						userlist.addElement(user);
					} else {
						break;
					}
				}
			}
			rs.close();
			return true;
		} catch (SQLException e) {
			System.out.print(e.getMessage());
			return false;
		}

	}

	public boolean checkPasswd(String inName, String inPasswd) throws Exception {
		boolean flag = false;
		ResultSet r = null;
		String sqlQuery = "select password from customers where name='"
				+ inName + "'";

		try {
			r = myConnection.runQuery(sqlQuery);
			r.next();
			String tempPd = r.getString("password");
			if (tempPd.equals(inPasswd))
				flag = true;
		} catch (SQLException e) {
			flag = false;
		}
		return flag;
	}

	public boolean updatePasswd(String inName, String inPasswd)
			throws Exception {

		sqlStr = "update customers set ";
		sqlStr = sqlStr + "password = '" + inPasswd + "' ";
		sqlStr = sqlStr + " where name = '" + inName + "'";
		try {
			//System.out.println(sqlStr);
			myConnection.runUpdate(sqlStr);

			return true;
		} catch (SQLException e) {
			return false;
		}
	}

	//修改用戶
	public boolean update(Customers inUser) throws Exception {

		sqlStr = "update customers set ";
		sqlStr = sqlStr + "password = '" + inUser.getPassword() + "',";
		sqlStr = sqlStr + "email = '" + inUser.getEmail() + "',";
		sqlStr = sqlStr + "sex = '" + inUser.getSex() + "',";
		sqlStr = sqlStr + "phone = '" + inUser.getPhone() + "',";
		sqlStr = sqlStr + "mobilephone = '" + inUser.getMobilePhone() + "',";
		sqlStr = sqlStr + "state = '" + inUser.getState() + "',";
		sqlStr = sqlStr + "province = '" + inUser.getProvince() + "',";
		sqlStr = sqlStr + "city = '" + inUser.getCity() + "',";
		sqlStr = sqlStr + "street = '" + inUser.getStreet() + "',";
		sqlStr = sqlStr + "age = " + inUser.getAge() + " ";
		sqlStr = sqlStr + " where name = '" + inUser.getName() + "'";
		try {

			myConnection.runUpdate(sqlStr);
			return true;
		} catch (SQLException e) {
			System.out.print(e.getMessage());
			return false;
		}

	}

	//刪除用戶
	public boolean delete(String inName) throws Exception {
		sqlStr = "delete from customers where name = '" + inName + "'";
		try {
			myConnection.runUpdate(sqlStr);
			return true;
		} catch (SQLException e) {
			System.out.println(e);
			return false;
		}
	}

	//查詢指定id的用戶，用于支持頁面的查看詳細資料請求
	public boolean getUserinfo(String inName) throws Exception {
		try {
			sqlStr = "select  * from customers where name = '" + inName + "'";

			ResultSet rs = myConnection.runQuery(sqlStr);
			userlist = new Vector();
			while (rs.next()) {
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setSex(rs.getString("sex"));
				user.setPhone(rs.getString("phone"));
				user.setMobilePhone(rs.getString("mobilePhone"));
				user.setState(rs.getString("state"));
				user.setProvince(rs.getString("province"));
				user.setCity(rs.getString("city"));
				user.setStreet(rs.getString("street"));
				user.setAge(rs.getInt("age"));
				user.setAccount(rs.getDouble("account"));
				userlist.addElement(user);
			}
			rs.close();
			return true;
		} catch (Exception e) {
			System.out.print(e.getMessage());
			return false;
		}
	}

	public int getPage() { //顯示的頁碼
		return page;
	}

	public void setPage(int newpage) {
		page = newpage;
	}

	public int getPageSize() { //每頁顯示的圖書數
		return pageSize;
	}

	public void setPageSize(int newpsize) {
		pageSize = newpsize;
	}

	public int getPageCount() { //頁面總數
		return pageCount;
	}

	public void setPageCount(int newpcount) {
		pageCount = newpcount;
	}

	public long getRecordCount() {
		return recordCount;
	}

	public void setRecordCount(long newrcount) {
		recordCount = newrcount;
	}

	public String getMessage() {
		return message;
	}

	public void setMessage(String msg) {
		message = msg;
	}

	public void setUserName(String uName) {
		username = uName;
	}

	public String getUserName() {
		return username;
	}

	public Vector getUserlist() {
		return userlist;
	}

	/* public static void main(String args[]){ 
	 try{
	 Eshop e= new Eshop();
	 if(e.checkPasswd("qwert", "123456")){
	 System.out.println("0000");
	 }
	 else System.out.println("111");
	 if(e.updatePasswd("qwert", "0000000")){
	 System.out.println("chenggonh");
	 }
	 else System.out.println("fail");
	 }catch(Exception e){ 
	 System.out.println("12468978");
	 e.printStackTrace();
	 }
	 
	 }
	 */

}